Search This Blog

SELECTing the NUMERIC Values from a Column

I saw several people asking this question in several forums. I will explain three different methods of doing this.

1. ISNUMERIC()

       The first method uses the built in 'ISNUMERIC()' function. This will of course returns the numeric values, but if any of these values contain a '$' symbol, the ISNUMERIC() function returns a 1.

 

2. The second approach is by using a LIKE clause.

3. Using a user defined function like this

CREATE FUNCTION dbo.fnIsNumeric (
@inString varchar(8000)
) RETURNS bit
AS

BEGIN
DECLARE @newstring varchar(1000),@i int
SELECT @i = 1

WHILE @i < len(@inString)+1
BEGIN
IF NOT (SUBSTRING(@inString, @i, 1) >= '0' and SUBSTRING(@inString, @i, 1) <= '9')
RETURN 0
SET @i = @i + 1
END
RETURN 1
END


Here is an example


SET NOCOUNT ON 
DECLARE @Tab TABLE ( val VARCHAR(100) )
INSERT INTO @Tab SELECT '$10' UNION ALL SELECT '$' UNION ALL SELECT '123' UNION ALL SELECT '100'

SELECT * FROM @Tab WHERE ISNUMERIC(Val) = 1
SELECT * FROM @Tab WHERE VAL NOT LIKE '%[^0-9]%'
SELECT * FROM @Tab WHERE dbo.fnIsNumeric(val) = 1

No comments: