Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Urgent! - How to extract number from a string Expand / Collapse
Author
Message
Posted Thursday, November 15, 2007 3:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:28 AM
Points: 7,179, Visits: 15,775
I've been beating the heck out of this drum lately - but an actual regular expression will help you find all of the various issues you're looking at here.

If you're using 2000, someone has been kind enough to create REGEX support in XP's which can then be added to your server. If you're on 2005 - build a CLR function (I've posted 4 versions in the last 2-3 days), which will definitively pick up these various issues patterns you're looking for.

Check out the scripts in here:

http://www.sqlservercentral.com/Forums/Topic419472-65-1.aspx


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #422834
Posted Thursday, November 15, 2007 5:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Matt is correct... he's beat the snare right off that drum... and guess what? He's right! Matt and I have done a huge amout of testing and using a RegEx "xp" in SQL Server 2000 is definitely a great way to go...

On the off chance that you have a DBA that refuses to allow a "non-MS XP", there's a fairly easy way to do it... the basis of the method is covered in the same thread that Matt sited. But, for everyone's convenience, here it is...

First, you need a Tally table... it's nothing more than a table with a single column of well indexed sequential numbers... you can make it as a Temp Table or, better yet, add it to your database as a permanent table. It's got a lot of uses so I recommend the latter. Here's how to make one...

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC

Next, you'll need a function... here's a pretty useful generic function that you can "program" to return your desired result...
 CREATE FUNCTION dbo.fnLeaveOnly 
/***********************************************************************************
Purpose:
This function accepts a string, the "LIKE" pattern that all characters must match
to be "kept", and a value to return if a NULL is the result of the function.

-- Jeff Moden
***********************************************************************************/
--===== Define the I/O Parameters
(
@String VARCHAR(8000), --String to be cleaned
@CharPattern VARCHAR(100), --Pattern a character must meet to keep
@NullValue VARCHAR(100) --Return this if a NULL is the result
)
RETURNS VARCHAR(8000) --The "cleaned" string
AS
BEGIN
--===== Declare the return variable
DECLARE @Return VARCHAR(8000)

--===== Clean the string leaving only what's in the character pattern
SELECT @Return = ISNULL(@Return,'')+SUBSTRING(@String,N,1)
FROM dbo.Tally
WHERE N <= LEN(@String)
AND SUBSTRING(@String,N,1) LIKE @CharPattern

--===== Return the "Cleaned" string substituting the null value if result is null
RETURN ISNULL(@Return,@NullValue)
END

Now, let's setup a test with all the values you stated and see what happens... do notice the "pattern" used...
DECLARE @yourtable TABLE (StarRating VARCHAR(100))
INSERT INTO @yourtable (StarRating)
SELECT '0' UNION ALL
SELECT '1' UNION ALL
SELECT '1.5' UNION ALL
SELECT 'NA' UNION ALL
SELECT '2' UNION ALL
SELECT '2 STAR' UNION ALL
SELECT ' ' UNION ALL
SELECT '3.00' UNION ALL
SELECT '4-STAR' UNION ALL
SELECT '4' UNION ALL
SELECT NULL UNION ALL
SELECT '~`!@#$%^&*()-_=+\|[{]};:''", /?a1_b2C3$'

SELECT StarRating AS Original,
dbo.fnLeaveOnly(StarRating,'[0-9.]',0)
FROM @yourtable

Result:
(12 row(s) affected)

Original Cleaned
----------------------------------------- ----------
0 0
1 1
1.5 1.5
NA 0
2 2
2 STAR 2
0
3.00 3.00
4-STAR 4
4 4
NULL 0
~`!@#$%^&*()-_=+\|[{]};:'", /?a1_b2C3$ .123

(12 row(s) affected)

Hope that helps...


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #422856
Posted Thursday, November 15, 2007 8:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 5, 2008 12:32 PM
Points: 16, Visits: 81
Thank you.
Post #422876
Posted Friday, November 16, 2007 5:44 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 9:28 AM
Points: 660, Visits: 323
Thats beautiful work guys!

What if the record has the string '4-Stars out of 5'. Woud this return 45?

I was thinking about creating a function which loops through the string one character at a time and does some math with the results.

1. You would need a try catch block to error trap and then exit out.
2. You would have to store the previous result to use when an error is found so you could roll back to it.
3. You may have to check for sequences such as 10E which might get interpreted as Scientific Notation.
Post #422995
Posted Friday, November 16, 2007 6:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Heh... Yes... it would return "45" on "4 out of 5 stars"... it wasn't designed to make "descisions"... current function was only designed to return characters that meet a pattern.

I agree... you could add functionality to return numbers as separate numbers in a table variable if they are separated by any non-numeric characters. You could even make it find things like "10R3" and "3D5" and do other ISNUMERIC extractions. And, you can do it all without an explicit loop as this function does or, you can use a loop.

Just remember, the more functionality you add, the slower the function will be... better to write a more specific function to meet your expected needs.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #423021
Posted Sunday, November 18, 2007 10:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, November 27, 2010 11:14 PM
Points: 356, Visits: 99
Hi,
You can try the following query :
select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.

Hope that Helps :)
Post #423430
Posted Monday, November 19, 2007 5:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 7:27 AM
Points: 35,769, Visits: 32,437
Hi,
You can try the following query :
select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.


Avaneesh,

Try this and see why you shouldn't use ISNUMERIC as an "IsAllDigits" function...

SELECT ISNUMERIC('1e3')
SELECT ISNUMERIC('1d3')
SELECT ISNUMERIC('$1,000.00')


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #423530
Posted Monday, November 19, 2007 5:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 9:28 AM
Points: 660, Visits: 323
There is a problem with using ISNumeric to decipher a string. Certain character groups return TRUE such as 10E. So if in this case someone lives at apartment 10E you would get a positive result for 10E when only 10 waas wanted or expected.

I think the solutions proposed by Matt & Jeff are as close as you can get with this type of mess. Software can only do so much to clean up unknown data, and no matter how you decide to squeeze the results you'll always leave something out.
Post #423533
Posted Monday, November 19, 2007 10:04 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 4:01 AM
Points: 734, Visits: 645
How about this. Might be a bit long winded though. This will pull out 742.0 of the following strings

'On 742.0 Evergreen Terrace'
'The number is at the end 742.0'
' 742.0 Evergreen Terrace 1234'

It only picks up the first full number it comes across and then exits the loops. Though if your using null values you may have to play with it.

declare @str varchar(100)
declare @tmp varchar(1)
declare @numstr varchar(100)
declare @count int
declare @fndnum binary

select @str=' 742.0 Evergreen Terrace'
select @count=1,@fndnum=0,@numstr=''

while @fndnum=0
begin
select @tmp=right(left(@str,@count),1)
print @tmp
select @count=@count+1
if isnumeric(@tmp)=1
while isnumeric(@tmp)=1
begin
select @numstr=@numstr+@tmp
select @tmp=right(left(@str,@count),1)
print @numstr
select @count=@count+1
select @fndnum=1

end
if @count>len(@str) set @fndnum=1
end

select 'I have found the number of ' + @numstr




Nuke the site from orbit, its the only way to be sure...
Post #423725
Posted Thursday, July 17, 2014 10:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 PM
Points: 16, Visits: 7
thank you very much..
Post #1593901
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse