Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieve numbers from a string


Retrieve numbers from a string

Author
Message
Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 865
Comments posted to this topic are about the item Retrieve numbers from a string

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 865
I noticed if the number was at the end of a sentence, it would not work. For example 'this is 9.00.', the period would mess it up. So I added a check for that right before the check for more than 1 decimal:

if right(@charnum,1) = '.' begin
set @charnum = left(@charnum,len(@charnum)-1)
end
-- don't convert numbers with more than 1 decimal
if len(replace(@charnum,'.','. ')) - len(@charnum) <= 1
begin
-- convert to float, insert into table
insert into @numbers values (@cnt,convert(float,@charnum))
end

That seemed to fix most of it, but I still have a problem with it now converting the string '9.00.' into 90. I have not had time to work on this again, so if you know a solution, I would be thankful!

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
Was looking at this and thinking there has to be a set-based way to acheive the same thing.
This is what I've come up with so far. Might be useful to read Jeff Modens article on Tally tables first (link in my sig below) if you're not already familiar with it.

I've omitted a few things on purpose so as not to obscure the main details of the query, like casting to decimal and checking for invalid strings with ISNUMERIC.

DECLARE @t TABLE(test varchar(8000))

INSERT @t
SELECT ' 1 iop 2?,.3e[]-4-@135.678;0--0;0...0'

SELECT
N,
number = SUBSTRING(test,N,PATINDEX('%[-.0123456789][^.0123456789]%',SUBSTRING(test+',',N,LEN(test)-N+2)))
FROM
@t, Tally
WHERE
N <= LEN(test) -- Restrict rows to the number of cahracters in the string
AND
CHARINDEX(SUBSTRING(test,N,1),'-.0123456789') > 0 -- Only rows starting with a valid numeric start character
AND
(N=0 OR CHARINDEX(SUBSTRING(test,N-1,1),'-.0123456789') = 0) -- either at the start of the string or following an invalid character.



This is basically an extrapolation of Jeffs string-splitting method.
In some basic tests I've done it's outperformed the looping method by a factor of 7 or 8.

I'm sure there are even faster methods and I've no doubt some of the more experienced posters on this site will be along to show us some CLR or XML method that's way beyond me :-)

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 865
Thanks Nigel, I got to play with this a little today. I have read Jeff's Tally table article before, good stuff! I knew there had to be a good set based way to do it. I will try and tweak it as I have time.

Thank you again :-D

____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog

nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 2823
No problem, thanks for the feedback.

Hope other people find it useful too.

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8708 Visits: 885
Thanks for the script.
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