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

String search Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2008 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 18, Visits: 664
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.



Post #534561
Posted Tuesday, July 15, 2008 10:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
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.
Post #534573
Posted Tuesday, July 15, 2008 10:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #534577
Posted Tuesday, July 15, 2008 11:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 18, Visits: 664
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?



Post #534588
Posted Tuesday, July 15, 2008 11:27 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
Would you alwys find "PPS" and "PAGES" on all your strings ?



* Noel
Post #534596
Posted Tuesday, July 15, 2008 11:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 18, Visits: 664
yes


Post #534598
Posted Tuesday, July 15, 2008 3:01 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 5:51 AM
Points: 6,266, Visits: 2,028
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
Post #534773
Posted Tuesday, July 15, 2008 7:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:07 AM
Points: 18, Visits: 664
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



Post #534832
Posted Tuesday, July 15, 2008 10:24 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:02 AM
Points: 3,241, Visits: 4,996
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/



Atif Sheikh


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #534857
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse