July 15, 2008 at 10:42 am
Fun problem,
I have a string like
"ANALYSIS RECORDS (APPROX 100 PPS)" OR "REVIEW MANUAL (75+ PAGES)"
What I need is the numeric value returned, in first string return would be 100, second string would be 75.
Any help would be appreciated.
July 15, 2008 at 10:56 am
Here's one way...
--sample data
declare @t table (s varchar(100))
insert @t
select 'ANALYSIS RECORDS (APPROX 100 PPS)'
union all select 'REVIEW MANUAL (75+ PAGES)'
union all select 'Number 11'
union all select '1 way'
union all select '7'
--calculation
select s, substring(s2, 0, j) as Number from (
select *, patindex('%[^0-9]%', s2 + 'x') as j from (
select *, substring(s, i, 100) as s2 from (
select *, patindex('%[0-9]%', s) as i from @t) a) b) c
/* results
s Number
---------------------------------------- --------
ANALYSIS RECORDS (APPROX 100 PPS) 100
REVIEW MANUAL (75+ PAGES) 75
Number 11 11
1 way 1
7 7
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 15, 2008 at 10:59 am
Check out this discussion:
http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx
Has a whole bunch of solutions, tests, etc., for this topic.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 15, 2008 at 11:21 am
Thanks for your help,
but when the string is like this "'RECORD ANALYSIS ON 1/1/2008 (APPROX 400 PPS)'" it returns the date portion. Not the page count, any ideas?
July 15, 2008 at 11:27 am
Would you alwys find "PPS" and "PAGES" on all your strings ?
* Noel
July 15, 2008 at 11:30 am
yes
July 15, 2008 at 3:01 pm
Ugly, I know but it could get you closer to the truth 🙂
declare @t table (s varchar(100))
insert @t (s)
select 'ANALYSIS RECORDS ON 1/1/2008 (APPROX 100 PPS)'
union all select 'REVIEW MANUAL (750+ PAGES)'
union all select 'ANALYSIS RECORDS (APPROX 108 PPS)'
union all select 'REVIEW MANUAL (78+ PAGES)'
select case when patindex('%PPS)%',s) > 0 then substring(s, patindex('% PPS)%',s) -
patindex('%[^0-9]%',
reverse(
left(s, patindex('% PPS)%',s)-1 )
)
) + 1 ,len(s) - patindex('% PPS)%',s)-1
)
when patindex('%PAGES)%',s) > 0 then replace(substring(s, patindex('% PAGES)%',s) -
patindex('%[^0-9+]%',
reverse(
left(s, patindex('% PAGES)%',s)-1 )
)
) + 1 ,len(s) - patindex('% PAGES)%',s)-2
),'+','')
else '-1' end , s
from @t
* Noel
July 15, 2008 at 7:44 pm
Noel,
thanks, it looks like it will get the job done. I'll play around w/ it.
I've tried about a dozen different solutions, a brain teaser is fun sometimes.
Thanks again
July 15, 2008 at 10:24 pm
You can also user TALLTY Table in this case.
Thiswill parse your string character wise. Parsing can also be done by many ways, but 'tally table' strategy is fast.
After parsing the string character wise, you can use ISNumeric function and get the desired results.
Here is the link... Might be helpful for future use.
http://www.sqlservercentral.com/articles/TSQL/62867/">
http://www.sqlservercentral.com/articles/TSQL/62867/
Atif Sheikh
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply