Post reply

Extract String from Column and place portions in additional columns

  • November 22, 2013 at 1:51 pm

    #282836

    Here's some code to help you understand what I'm dealing with...

    CREATE TABLE TestNoteText ( TextValue TEXT )

    INSERT INTO TestNoteText

    VALUES

    ( '[Stop...[<here>] [Go...[<here>] [Go Ahead...[<here>] [End...[<here>]' )

    SELECT

    *

    FROM

    [dbo].[TestNoteText] AS tnt

    What I want to do is capture separate portions of this text and move it around in a Crystal Report. I tried Crystal syntax, but it's not really working out. So I thought SQL Server would work easier.

    The [Stop..., [Go..., [Go Ahead..., and [End... will always be the same. The ] at the end of each will always be the same. The information in ...[<here>] will always be different.

    What I need is a select statement that pulls four columns. Column 1 = [Stop...[<here>], Column 2 = [Go...[<here>], Column 3 = [Go Ahead...[<here>], and Column 4 = [End...[<here>]

    Any ideas?

  • November 22, 2013 at 2:27 pm

    #1668540

    I'm not sure if SQL Server will be efficient on managing this, but it seems that this code should do the work. Note that I had to cast the text value to manipulate it as a string, but I would strongly suggest you to change it into a varchar(max) or even better a normal varchar because text is deprecated and varchar(max) might not perform as well as a varchar(8000) or smaller.

    WITH CTE AS(

    SELECT CAST( TextValue AS varchar(max)) TextValue

    FROM #TestNoteText

    )

    SELECT TextValue,

    LEFT( TextValue, CHARINDEX( ']', TextValue)),

    SUBSTRING( TextValue, CHARINDEX( '[Go', TextValue), CHARINDEX( ']', TextValue, CHARINDEX( '[Go', TextValue)) - CHARINDEX( '[Go', TextValue) + 1),

    SUBSTRING( TextValue, CHARINDEX( '[Go Ahead', TextValue), CHARINDEX( ']', TextValue, CHARINDEX( '[Go Ahead', TextValue)) - CHARINDEX( '[Go Ahead', TextValue) + 1),

    SUBSTRING( TextValue, CHARINDEX( '[End', TextValue), CHARINDEX( ']', TextValue, CHARINDEX( '[End', TextValue)) - CHARINDEX( '[End', TextValue) + 1)

    FROM CTE AS tnt;

    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

  • November 22, 2013 at 2:56 pm

    #1668548

    B-E-A-utiful. Thank you very much!

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

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