Post reply

Output substring between delimiters with varying length

  • June 11, 2012 at 5:24 am

    #275657

    Hi All,

    Hope you can help me, my SQL experience is very limited so you'll have to bear with me!

    I have a table containing various info of which is a file location column e.g.

    H:\FOLDER\123456\document1.doc

    H:\FOLDER\1234\document2.doc

    H:\FOLDER\98765\document1.doc

    etc.

    What i want to do is pull from SQL the '123456 / '1234' / '98765' etc. from this field and create a new column containing this string.

    Any ideas? I seem to think its SUBSTRING but im not sure how to do it with delimiters as that seems to just go on length only.

    The H:\FOLDER will always be the same. Its the next sub folder that i need and can be of varying length.

    Kind Regards

    Brendan

  • June 11, 2012 at 5:38 am

    #1499255

    Probably a better way to do it but heres my first quick go

    declare @tab table (filepath nvarchar(100))

    insert into @tab values ('H:\FOLDER\123456\document1.doc'),

    ('H:\FOLDER\1234\document2.doc'),

    ('H:\FOLDER\98765\document1.doc')

    SELECT

    Filepath,

    LEFT(REPLACE(filepath,'H:\Folder\',''),(CHARINDEX('\',REPLACE(filepath,'H:\Folder\',''),1)-1))

    FROM

    @tab

  • June 11, 2012 at 5:49 am

    #1499259

    That seems to work many thanks, however, how do i get SQL to pull the data from a column in a table in a database rather than passing it strings?

    e.g. my database is called test, the table is table1 and the column is filepath?

    Regards

    Brendan

  • June 11, 2012 at 5:55 am

    #1499262

    change the FROM clause to point to your table in question

  • June 11, 2012 at 7:07 am

    #1499291

    Of course doh! I said i was a newbie so forgive me O:)

    Ive now got that to work, heres my query as it is:

    SELECT

    ContentReference,

    LEFT(REPLACE(ContentReference,'H:\DAT\',''),(CHARINDEX('\',REPLACE(contentreference,'H:\DAT\',''),7)-1))

    FROM

    dbo.Payloads

    In the Payloads table there is a column called ID

    How do i get the output of the query above to be populated into the ID column?

    Regards

    Brendan

  • June 11, 2012 at 7:10 am

    #1499294

    Lookup UPDATE in BOL or a search engine.

    But basically you want to do a self join

    UPDATE t1

    SET t1.ID = LEFT(........t2.FilePath......)

    FROM table t1

    INNER JOIN

    table t2

    ON

    t1.FilePath = t2.FilePath

Viewing 6 posts - 1 through 6 (of 6 total)

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