Select only the number from a text+numbers string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select only the number from a text+numbers string

Author

Topic

glpita
Starting Member

17 Posts

Posted - 2010-02-10 : 14:22:15

Hi,

I need to write a query to select only the number from a field like this:

TWO STORY MASONRY (12) UNIT BLDG or
ONE STORY TIMBER 3 UNIT BLDG

Any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:48:18

here's a two step solution


SELECT STUFF(Val1,PATINDEX('%[^0-9]%',Val1),LEN(Val1),'')
FROM
(
SELECT  Val,STUFF(Val,1,PATINDEX('%[0-9]%',Val)-1,'') As Val1
FROM
(
SELECT 'TWO STORY MASONRY (12) UNIT BLDG' AS Val UNION ALL 
SELECT 'ONE STORY TIMBER 3 UNIT BLDG' 
)t
)r

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-02-10 : 14:49:21

DECLARE @inString varchar(100)
DECLARE @outString varchar(10)
DECLARE @outNumber int

SET @inString = 'ONE STORY TIMBER 3 UNIT BLDG'
SET @outString = ''

WHILE LEN(@instring)> 0
BEGIN
IF SUBSTRING(@inString,1,1) LIKE '%[0-9]%' SET @outString = @outString + SUBSTRING(@inString,1,1)
SET @inString = Substring(@instring,2,100)

END

SET @outNumber = convert(int,@outString

I'm sure there's a way to do this without the while loop, but this will for now
Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-02-10 : 14:55:55

Man, I hit F5 several times before I went ahead and posted!
DECLARE @inString varchar(100)
DECLARE @outString varchar(10)
DECLARE @outNumber int

SET @inString = 'TWO STORY MASONRY (12) UNIT BLDG'
SET @outString = ''

SELECT @outString = @outString +
CASE WHEN substring(@inString,spt.number,1) like '[%0-9%]'
THEN substring(@inString,spt.number,1)
ELSE ''
END
FROM
master..spt_values spt

WHERE
spt.[type] = 'P' and spt.number < LEN(@inString)

SET @outNumber = convert(int, @outstring)

SELECT @outnumber

Jim

Everyday I learn something that somebody else already knew

glpita
Starting Member

17 Posts

Posted - 2010-02-10 : 19:53:50

Thanks a lot guys