Post reply

Extract String between Special characters

  • July 10, 2013 at 4:23 pm

    #277258

    Hi All,

    I have a string like this

    Building Management - Ex. Existing Facility\Keys & locks\Project Co to ensure that 400 complete sets of keys are available at all times.

    now my requirement is to extract string before first \ ,second \ and third \ . So my results would be like this

    Building Management - Ex. Existing Facility

    Keys & locks

    Project Co to ensure that 400 complete sets of keys are available at all times.

    what is best way of achieving this in sql server 2008.

  • July 10, 2013 at 4:54 pm

    #1631440

    The best way that comes to my mind is to use the 8K Splitter. Read the following article and come back if you need help.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 11, 2013 at 2:32 am

    #1631491

    As long as you don't have more then four fields, you can use parsename, although this function is specifically intended to parse object names in SQL Server and only accepts '.' as the delimiter. But first place the dots with some other character and then the backslashes with dots:

    DECLARE @STR nvarchar(4000) = 'Building Management - Ex. Existing Facility\Keys & locks\Project Co to ensure that 400 complete sets of keys are available at all times.'

    ; WITH replacements AS (

    SELECT replace(replace(@str, '.', char(31)), '\', '.') AS str

    )

    SELECT replace(parsename(str, 3), char(31), '.'),

    replace(parsename(str, 2), char(31), '.'),

    replace(parsename(str, 1), char(31), '.')

    FROM replacements

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

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

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