Extract String from Column and place portions in additional columns

• SQL_Enthusiast

  Hall of Fame

  Points: 3805

  More actions

  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?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  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

• SQL_Enthusiast

  Hall of Fame

  Points: 3805

  More actions

  November 22, 2013 at 2:56 pm

  #1668548

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