Post reply

Can someone help me with the following TSQL search string extract?

  • January 6, 2013 at 4:19 pm

    #268914

    I would like to extract Tow (Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat|) information from the string below:

    8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat|

    I would like to be able to use the following code if possible: TableName: policies Field is: Name

    center][/center], Declare

    center][/center], @data varchar(200),

    center][/center], @position integer

    center][/center], set @data =

    center][/center], set @position =

    Select(right(p.Name, len(@data) , @position– (PathIndex(‘%[0-9}%’, @data)-1))

    From policies p

    Any suggestion would be greatly appreciated.

    Regards,

    leonie6214

  • January 6, 2013 at 5:20 pm

    #1574152

    How about something like this instead?

    ;WITH Notes (Note) AS (

    SELECT '8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat| ')

    SELECT Item

    FROM Notes

    CROSS APPLY DelimitedSplit8K(Note, '|')

    WHERE CHARINDEX('Tow', Item) <> 0

    DelimitedSplit8K is the community, high-performance string splitter popularized by Jeff Moden, which can be found here: Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • January 6, 2013 at 5:34 pm

    #1574155

    Thanks for providing this solution. I will respond and let you know if it works out.

    Regards,

    leonie6214

  • January 7, 2013 at 10:46 am

    #1574430

    SSC Rookie..

    Try this out-

    declare @string nvarchar(max)

    select @string = '8Am to 6PM Mon-Fri| |Tow: 3PM to 8PM Mon-Fri, 4AM -11PM Fri-Sat| Clean: 9AM to 11PM Mon-Fri, 4AM -7AM Fri-Sat| '

    select substring(@string,patindex('%Tow:%',@string),patindex('%Clean:%',substring(@string,patindex('%Tow:%',@string),len(@string)))-3)

    -J

Viewing 4 posts - 1 through 3 (of 3 total)

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