Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Retrieve numbers from a string Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, February 16, 2010 10:36 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, May 19, 2016 12:44 PM Points: 570, 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
Post #866240
 Posted Thursday, March 4, 2010 7:47 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, May 19, 2016 12:44 PM Points: 570, 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)) endThat 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
Post #876844
 Posted Monday, March 8, 2010 4:59 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, September 29, 2016 7:09 AM Points: 1,276, Visits: 2,820
 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 @tSELECT ' 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
Post #878521
 Posted Tuesday, March 9, 2010 2:53 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, May 19, 2016 12:44 PM Points: 570, 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 ____________________________________________________________________________________________Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
Post #879769
 Posted Wednesday, March 10, 2010 1:52 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, September 29, 2016 7:09 AM Points: 1,276, Visits: 2,820
 No problem, thanks for the feedback. Hope other people find it useful too.
Post #880008
 Posted Thursday, May 12, 2016 7:31 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 7:11 AM Points: 8,298, Visits: 852
 Thanks for the script.
Post #1785707

 Permissions