ISNUMERIC Case expression help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ISNUMERIC Case expression help

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-21 : 07:21:50

I have the the following piece of SQL

COALESCE ( REPLACE ( STR( coSalesCatalogI.SCtlgIndCode, 14 ), ' ', '0' ), '00000000000000' )

which can only handle NULL, 0, ' ' or numerics at present but there are new text entries in this field so i'm trying to write a test case expression(below) which can handle each scenario as it does at the min but if the field is text i just want to return it without doing any formatting to it.


DECLARE @NUM  VARCHAR(20),
	@NUM1 VARCHAR(20),
	@NUM2 VARCHAR(20),
	@NUM3 VARCHAR(20),
	@NUM4 VARCHAR(20)

SET @NUM = '0'
--SET @NUM = ' '
--SET @NUM = '035600701300544'
--SET @NUM = 'EAN-14  FUN PARK'
--SET @NUM = 'NULL'

SELECT CASE @NUM
	    WHEN ('0', 'NULL', ' ') 
		THEN '00000000000000'
	    WHEN ISNUMERIC (@NUM) = 1
		THEN REPLACE (STR( @NUM, 14 ), ' ', '0' )
	    WHEN ISNUMERIC (@NUM) = 0
		THEN @NUM
	   END

Any help would be appreciated.

Sachin.Nand

2937 Posts

Posted - 2010-12-21 : 07:33:01

This?


DECLARE @NUM  VARCHAR(20),
	@NUM1 VARCHAR(20),
	@NUM2 VARCHAR(20),
	@NUM3 VARCHAR(20),
	@NUM4 VARCHAR(20)

--SET @NUM = '0'
--SET @NUM = ' '
--SET @NUM = '035600701300544
SET @NUM = 'EAN-14  FUN PARK'
--SET @NUM = 'NULL'

SELECT 
CASE when len(@NUM)>0 
 then
	           case when @NUM='0'  THEN '00000000000000'
	                when ISNUMERIC (@NUM) =1 THEN  REPLACE (STR( @NUM, 14 ), ' ', '0' )
	                when ISNUMERIC (@NUM) = 0  THEN @NUM

 end
		
	   END

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-21 : 08:26:57

Note that isnumeric is not reliable

select isnumeric(','),isnumeric('$'),isnumeric('12d3')

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-21 : 09:03:16

Thanks for your help on this Sachin.Nand & madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources