String search

  • 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.

  • 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.

  • 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

  • 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?

  • Would you alwys find "PPS" and "PAGES" on all your strings ?


    * Noel

  • yes

  • 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

  • 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

  • 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

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply