TexCopy Sql Server 2005

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