SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Urgent! - How to extract number from a string


Urgent! - How to extract number from a string

Author
Message
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12523 Visits: 18582
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87902 Visits: 41125
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
KMM-489657
KMM-489657
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 81
Thank you.
Robert O'Byrne
Robert O'Byrne
Say Hey Kid
Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)

Group: General Forum Members
Points: 710 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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87902 Visits: 41125
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Avaneesh -388582
Avaneesh -388582
Old Hand
Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)Old Hand (374 reputation)

Group: General Forum Members
Points: 374 Visits: 99
Hi,
You can try the following query :
select case isnumeric(YourFieldName) when 1 then YourFieldName else 0 end.

Hope that Helps Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87902 Visits: 41125
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Robert O'Byrne
Robert O'Byrne
Say Hey Kid
Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)

Group: General Forum Members
Points: 710 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.
EvilPostIT
EvilPostIT
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 648
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... w00t
phaniraj1987
phaniraj1987
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 8
thank you very much..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search