Click here to Skip to main content
15,868,125 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
I want a query which must give me only numbers for the given string.The given string is a combination of alphabets,numbers,symbols,special characters. Whatever may be the given string it should return only numbers.

For Example:

Declare @string varchar(max)
select @string = 'abc9-82nm@$%'

The desire output is 982.

Thanks in advance.
Posted
Comments
[no name] 6-Sep-13 6:32am    
You should go to www.vworker.com so that you can hire someone to do this work for you.

 
Share this answer
 
Comments
bbirajdar 6-Sep-13 6:43am    
5ed
Thomas ktg 6-Sep-13 6:44am    
thanks
AmitGajjar 6-Sep-13 6:45am    
i forgot to search for the solution instead i have start writing :) 5+ for you
Member 9848727 6-Sep-13 6:50am    
I have searched. I just want to achieve through query but not the function thats the reason why i posted .
AmitGajjar 6-Sep-13 6:54am    
thats great. keep it up.
SQL
DECLARE @strAlphaNumeric varchar(max)
SET @strAlphaNumeric = 'abc9-82nm@$%' 
SELECT @strAlphaNumeric AS InputString
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
 WHILE @intAlpha > 0
   BEGIN
     SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
     SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
   END
END
SELECT ISNULL(@strAlphaNumeric,0) AS [Output]
 
Share this answer
 
try this function....
SQL
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000))
RETURNS VARCHAR(1000)
AS
BEGIN
  DECLARE @Count INT
  DECLARE @IntNumbers VARCHAR(1000)
  SET @Count = 0
  SET @IntNumbers = ''

  WHILE @Count <= LEN(@String)
  BEGIN
    IF SUBSTRING(@String,@Count,1) >= '0' AND SUBSTRING(@String,@Count,1) <= '9'
        SET @IntNumbers = @IntNumbers + SUBSTRING(@String,@Count,1)
    
    SET @Count = @Count + 1
  END

  RETURN @IntNumbers
END

Run following script in query analyzer.
SQL
SELECT ExtractInteger('abc9-82nm@$%L')

It will return following values.
SQL
982

Instead of a function Single Query...
SQL
Declare @s varchar(100),@result varchar(100)
set @s='abc9-82nm@$%'
set @result=''

select @result=@result+case when number like '[0-9]' then number else '' end
from ( select substring(@s,number,1) as number
       from (select number from master..spt_values
             where type='p' and number between 1 and len(@s)
             ) as t
     ) as t

select @result as only_numbers

Output:
SQL
only_numbers
----------
982
 
Share this answer
 
v4
Comments
Member 9848727 6-Sep-13 6:46am    
Hey,
Thanku its working.
Is there any other way without using function?
Raja Sekhar S 6-Sep-13 6:47am    
Check the Updated Solution...
Hi,

I have write some code but not tested. you can follow the logic but i am not sure about my code will work fine without error.
SQL
DECLARE @COUNT INT
DECLARE @RESULT VARCHAR(MAX)
DECLARE @INPUTSTR VARCHAR(MAX)
SET @INPUTSTR = "YOUR INPUT STRING"
SET @COUNT = LEN(@INPUTSTR)

WHILE(@COUNT>0)
   BEGIN
   SET @COUNT = @COUNT - 1

   IF SUBSTRING(@INPUTSTR,@COUNT-1,1) LIKE [0-9]
   BEGIN
       SET @RESULT = SUBSTRING(@postal,2,1) + @RESULT
   END
END

RETURN @RESULT
GO

Best luck
-Amit Gajjar
 
Share this answer
 
v2
Please try this
SQL
ALTER FUNCTION dbo.ExtractInteger ( @String VARCHAR(2000) )
RETURNS VARCHAR(1000)
AS 
    BEGIN
        DECLARE @Count INT
        DECLARE @IntNumbers VARCHAR(1000)
        SET @Count = 0
        SET @IntNumbers = ''

        WHILE @Count <= LEN(@String) 
            BEGIN
                IF SUBSTRING(@String, @Count, 1) >= '0'
                    AND SUBSTRING(@String, @Count, 1) <= '9' 
                    BEGIN
                        SET @IntNumbers = @IntNumbers + SUBSTRING(@String,
                                                              @Count, 1)
                    END
                SET @Count = @Count + 1
            END

        RETURN @IntNumbers
    END
GO

SELECT dbo.ExtractInteger('dfsd7sd8g8sds')
 
Share this answer
 
v3
SQL
DECLARE @NonNumeric varchar(1000) =  'abc9-82nm@$%'
DECLARE @Index int
SET @Index = 0
while 1=1
begin
    set @Index = patindex('%[^0-9]%',@NonNumeric)
    if @Index <> 0
    begin
        SET @NonNumeric = replace(@NonNumeric,substring(@NonNumeric,@Index, 1), '')
    end
    else
        break;
end
select @NonNumeric as 'Value'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900