Parse numbers from string

Hello All,

I have to parse only numbers from a string and store them into two variables. The column where the string is really a free form. It can take spaces, wild chars, numbers etc.

for example -
5 %-10% =@num1 = 5 and @num2 = 10
20 %-30% =@num1 = 20 and @num2 = 30
25 -30 % = @num1 = 25 and num2 = 30
40% - 50  = @num1 = 40 and num2 = 50
How would I eliminate everything other than numbers and get the left part of number in @num1 and right side of number in @num2 variables.

Thanks,
-B
BrookKAsked:
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.

Philippe DamervalSenior Analyst ProgrammerCommented:
Are the "sides" always separated by hyphens (minus signs)?
HwkrangerCommented:
Use Regular Expressions.
Philippe DamervalSenior Analyst ProgrammerCommented:
Here's how to strip  non-numeric characters (thank you SQLServerCentral)




-- =============================================
-- Create scalar function fnStripNonnumericChars
-- Created by Michael Gladshteyn
-- =============================================
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fnStripNonnumericChars')
	DROP FUNCTION fnStripNonnumericChars
GO

CREATE FUNCTION fnStripNonnumericChars 
	(@OrigString varchar(255))
RETURNS varchar(255)
AS
BEGIN
/*
32 is a space 
48 to 57 are our numbers 0-9 
65 to 90 are the capital letters A-Z 
97 to 122 are the lowercase letters a-z 

-- usage: select master.dbo.fnStripNonnumericChars('12345 MB')
*/

declare @NewString varchar(255)


Declare @Len int, @Ctr As int, @Ctr2 As int, @Char As varchar(1)

select @Len = Len(@OrigString)
select @NewString=''

select @Ctr2 = 1, @Ctr=1

while @Ctr <= @Len
Begin
    Select @Char = substring(@OrigString, @Ctr, 1)
    If ASCII(@Char) between 48 and 57 
    begin 	
	Select @NewString=@NewString+@Char
        Select @Ctr2 = @Ctr2 + 1
    end
    select @Ctr=@Ctr+1
CONTINUE
end
If @Ctr2 = 1 
    Select @NewString = NULL


RETURN @NewString

END
GO
--------------------------------------------------
--------------------------------------------------
-- This is how you can use it

SET NOCOUNT ON
create table #spacetaken
(
[name] varchar(255),
rows int,
reserved varchar(20),
data varchar(20),
index_size varchar(20),
unused  varchar(20)
)
declare @Name varchar(255)
declare curTables scroll cursor for
--select [name] from dbo.sysobjects where id = object_id(N'[dbo].[temp2RptDReportTiers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

open curTables

fetch first from curTables into @Name

while (@@FETCH_STATUS <> -1)
begin
   if (@@FETCH_STATUS = 0)
   begin
	INSERT #spacetaken
      	exec sp_spaceused @Name
   end
   fetch next from curTables into @Name
end

deallocate curTables

Select [name]=convert(varchar(55),[name]), [rows], reserved,data,index_size,unused 
from #spacetaken order by convert(int,master.dbo.fnStripNonnumericChars(reserved)) desc
drop table #spacetaken
SET NOCOUNT OFF

Open in new window

Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

HainKurtSr. System AnalystCommented:
is that number always 2 digit?

can it be like

12-% 3 = @num1 ...
cyberkiwiCommented:

with t(col) as (
select '5 %-10%' union all select
'20 %-30%' union all select
'25 -30 %' union all select
'40% - 50' union all select
null union all select  -- exception condition null
'' union all select  -- exception condition blank
'  68' union all select  -- exception condition one number only
'--68%-78' union all select  -- does not start with number
'.')   -- exception condition non blank non numeric

,u as (select substring(col, patindex('%[0-9]%', col+'1'), 1000) col from t)
,v as (select left(col, patindex('%[^0-9]%', col+'.') -1) num1, col from u)
,w as (select num1, right(col, len(col) - len(num1)) col from v)
,x as (select num1, substring(col, patindex('%[0-9]%', col+'1'), 1000) col from w)
select
	cast(nullif(num1,'') as int) as num1,
	cast(nullif(left(col, patindex('%[^0-9]%', col+'.') -1),'') as int) num2
from x

Open in new window

cyberkiwiCommented:
As a function, see code box

Usage:

declare @num1 int, @num2 int
select @num1=num1, @num2=num2 from dbo.get2Numbers('5 %-10%')
select '@num1 and @num2 are: ', @num1, @num2
select @num1=num1, @num2=num2 from dbo.get2Numbers('40% - 50')
select '@num1 and @num2 are: ', @num1, @num2

create function get2Numbers(@col varchar(1000)) returns table as return
with u as (select substring(@col, patindex('%[0-9]%', @col+'1'), 1000) col)
,v as (select left(col, patindex('%[^0-9]%', col+'.') -1) num1, col from u)
,w as (select num1, right(col, len(col) - len(num1)) col from v)
,x as (select num1, substring(col, patindex('%[0-9]%', col+'1'), 1000) col from w)
select
	cast(nullif(num1,'') as int) as num1,
	cast(nullif(left(col, patindex('%[^0-9]%', col+'.') -1),'') as int) num2
from x
GO

Open in new window

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
HainKurtSr. System AnalystCommented:
below gives 50 if it is always "-"

declare @s as varchar(200) = '40% - 50  = @num1 = 40 and num2 = 50'
select
reverse(left(reverse(left(s, charindex('=@num1',s)-1)), charindex('-',reverse(left(s, charindex('=@num1',s)-1)))-1))
from (select replace(replace(@s,' ',''),'%','') as s) t
HainKurtSr. System AnalystCommented:
above sql first removes % and space characters and then uses charindex, left/right, substring, reverse functions to fint the number...
jorge_torizResearch & Development ManagerCommented:
I would create a C# assembly to perform search of numbers in a string and then return the list in XML file:

SQL Server:

CREATE ASSEMBLY SqlAssemblies
FROM 'Path_to_your_assembly_file.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION ParseNumbers(
      @OriginalString NVARCHAR(4000)
)
RETURNS XML
AS EXTERNAL NAME SqlAssemblies.[SqlAssemblies.MySqlFunctions].ParseNumbers

C#:

namespace SqlAssemblies
{
    public class MySqlFunctions
    {
        [SqlFunction]
        public static SqlXml ParseNumbers(SqlString originalString)
        {
            Regex regFindNumbers = new Regex(@"\d+");
            if (regFindNumbers.IsMatch(originalString.Value))
            {
                MatchCollection matches = regFindNumbers.Matches(originalString.Value);
                StringBuilder sb = new StringBuilder();
                sb.Append("<numbers>");
                foreach (Match m in matches)
                {
                    sb.Append("<number value=\"" + m.Value + "\" />");
                }
                sb.Append("</numbers>");

                StringReader sr = new StringReader(sb.ToString());
                XmlReader xr = XmlReader.Create(sr);
                return new SqlXml(xr);
            }
            else
                return null;
        }
    }
}
vinodchCommented:
CREATE FUNCTION ExtractInteger(@String VARCHAR(2000),@Delimeter char(1),@IsLeft bit)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Count INT
DECLARE @IntNumbers VARCHAR(1000)
DECLARE @ActualString VARCHAR(1000)

SET @Count = 0
SET @IntNumbers = ''
If @IsLeft = 1
Begin
      Select @ActualString = Left(@String,charindex(@Delimeter,@String)-1)      
End
Else
Begin
      Select @ActualString = Right(@String,len(@String) - charindex(@Delimeter,@String) )
End
WHILE @Count <= LEN(@ActualString)
BEGIN
IF SUBSTRING(@ActualString,@Count,1) >= '0'
AND SUBSTRING(@ActualString,@Count,1) <= '9'
BEGIN
SET @IntNumbers = @IntNumbers + SUBSTRING(@ActualString,@Count,1)
END
SET @Count = @Count + 1
END

RETURN @IntNumbers
END
GO

-- first parameter is the actualstring, second is the delimeter, and third is to get left part or right part
-- To get left of the string
Select dbo.ExtractInteger('g213123hfghfhg.g67567576','.',1)

-- to get right of the string
Select dbo.ExtractInteger('g213123hfghfhg.g67567576','.',0)
BrookKAuthor Commented:
Thanks for you reply.

yes the sides are always separated by '-' (dash)
cyberkiwiCommented:
BrookK,

You have quite a few comments, so may I suggest starting from the top?
And given the variability of your data, you may want to test using all the possible formats, I have made sure it works with all formats, even

'5 %-10%'
'20 %-30%'
'25 -30 %'
'40% - 50'
null
'' -- exception condition blank
'  68' -- exception condition one number only
'--68%-78' -- does not start with number
'.'

Regards
BrookKAuthor Commented:
Thank you all. Sorry I was not online to check all the responses.

I am going to check each reply for every possible scenario right now. Will update you with my results.

Thanks again,
-B
BrookKAuthor Commented:
Thank you all. I used cyberkiwi's code to implement the solution. I am still trying to digest it though. But it worked with all the possible strings user could enter.

One question, how can I make this function work for more than one inputs to the function. I have all the ranges stored in a table variable. Now I have to call this function to get two numbers from all the ranges and store them in a temp table.

How would I achieve this?

Thanks,
BrookKAuthor Commented:
I figured this out

Thank you all.
BrookKAuthor Commented:
Worked for all the scenario I know so far.
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 2005

From novice to tech pro — start learning today.