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


String search


String search

Author
Message
dave10509
dave10509
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 866
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.



RyanRandall
RyanRandall
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1813 Visits: 4652
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.
GSquared
GSquared
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16057 Visits: 9729
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
dave10509
dave10509
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 866
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?



noeld
noeld
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6852 Visits: 2048
Would you alwys find "PPS" and "PAGES" on all your strings ?


* Noel
dave10509
dave10509
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 866
yes



noeld
noeld
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6852 Visits: 2048
Ugly, I know but it could get you closer to the truth Smile

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
dave10509
dave10509
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 866
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



Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3363 Visits: 5175
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


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