Trying to pull just numeric values from a string.

I am trying to write some script to pull out the numeric values from a string then concatenate the string with only the numeric values.  The script that I have written is able to identify the numeric values but I am not able to concatenate them into a string.  I keep getting null for the output with the way I have this written right now.  any suggestions?

btw: I do know that I am calling the @out_string inside the loop.  I have it there for debugging purposes.

Thanks!

declare @i int,
            @in_string varchar(250),
            @char_counter int,
            @out_string varchar(250),
            @results varchar(250)

set @i = 1
set @in_string = '1a2b3c4d'
set @char_counter = 8

While @i <= @char_counter

 begin
 
      if substring(@in_string,@i,1) in ('0', '1','2', '3', '4', '5', '6', '7', '8', '9')

      set @out_string = replace(@out_string,@out_string,@out_string + substring(@in_string,@i,1))

      select @out_string
      select @i,@char_counter
 
   select @i = @i + 1
 end
Richard ComitoDirector of ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
I'd use a scalar function for that, trying to be as efficient as possible within a loop (you could write it without a loop, but to me it's not worth the effort for this trivial a task):

CREATE FUNCTION [dbo].[StripNonnumericChars] (
      @string varchar(200)
      )
RETURNS varchar(200)
AS
--Author: Scott Pletcher.
BEGIN
DECLARE @byte int
WHILE 1 = 1
BEGIN
      SET @byte = PATINDEX('%[^0-9]%', @string)
      IF @byte = 0
            BREAK
      SET @string = STUFF(@string, @byte, 1, '')
END --WHIILE
RETURN @string
END --FUNCTION

GO

SELECT dbo.StripNonnumericChars('8la;'/4.,><]3[\=-(*7')
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard ComitoDirector of ITAuthor Commented:
Thanks ScottPletcher!  That works perfectly.
0
dsackerContract ERP Admin/ConsultantCommented:
Create a function for this, as follows:
CREATE FUNCTION dbo.udf_GetNumeric
    (@String varchar(255) = NULL)
RETURNS varchar(255)
AS
BEGIN
    IF ISNULL(@String, '') = ''
        RETURN ''

    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @String)

    WHILE @intAlpha > 0
    BEGIN
        SET @String = STUFF(@String, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^0-9]%', @String )
    END

    RETURN ISNULL(@String,0)
END

Open in new window

Then simply call the function, as follows:
DECLARE @String varchar(50)

SET @String = 'ABC432DEF12A'

SELECT dbo.udf_GetNumeric(@String)

Open in new window

0
Rodney EndrigaData AnalystCommented:
The way your code is, @out_string is NULL to start with.

I added this line and got some results.
declare @i int,
             @in_string varchar(250),
             @char_counter int,
             @out_string varchar(250),
             @results varchar(250)

 set @i = 1
 set @in_string = '1a2b3c4d'
 set @char_counter = 8

 While @i <= @char_counter

  begin
   
      if substring(@in_string,@i,1) in ('0', '1','2', '3', '4', '5', '6', '7', '8', '9')

       set @out_string = substring(@in_string,@i,1)
	   set @out_string = replace(@out_string,@out_string,@out_string + substring(@in_string,@i,1))
       
       select @out_string
       select @i,@char_counter
	
   select @i = @i + 1
  end 

Open in new window


I'm just declaring the @out_string to be 'something' before using the REPLACE. You will just have to adjust it to want you want in the results.
0
Scott PletcherSenior DBACommented:
You're welcome, but let me add one thing

CORRECTION:

Need to handle NULL input values.  Easiest is to specify WITH option to return NULL immediately:

ALTER FUNCTION [dbo].[StripNonnumericChars] (
      @string varchar(200)
      )
RETURNS varchar(200)
WITH RETURNS NULL ON NULL INPUT
AS
...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.