Returning part of a string

• Eliza

  SSC Eights!

  Points: 967

  More actions

  October 19, 2013 at 5:06 pm

  #300293

  Hi All,

  I have a text field that as an example can look like

  '<Field>' Changed from '<value1>' to '<value2>' by '<Person>' on '<Date>'

  I would like to return this from the database but break down into separate columns the <field>, <value2>, <value2>, <Person> and <Date>

  I'm playing with SUBSTRING, PATINDEX and CHARINDEX but struggling with the logic. The string to search through will always follow the same format and have double quotes around the values with the <> characters as well. But it will vary in length.

  All values can be returned as string I do not need to convert or cast any.

  Can you help please.

  Eliza

• Jeff Moden

  SSC Guru

  Points: 1004312

  More actions

  October 19, 2013 at 9:35 pm

  #1659940

  If the audit strings won't ever be longer than 8K, here's one way. It's quite generic so it won't necessarily be the fastest method but will still perform quite well and is quite easy if you understand what a Tally Table does.

  First, create the following function...

  CREATE FUNCTION dbo.TokenizeAuditString

  (@pString VARCHAR(8000))

  RETURNS TABLE WITH SCHEMABINDING AS

  RETURN WITH

  --========== Generate up to 10K rows ("En" indicates the power of 10 produced)

  E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))n(N)),

  E4(N) AS (SELECT 1 FROM E1 a,E1 b,E1 c, E1 d),

  cteTally(N) AS (SELECT TOP (LEN(@pString)) ROW_NUMBER()OVER(ORDER BY N) FROM E4),

  cteSplit AS

  ( --======== Split the string on the delimiters of '< and >'

  SELECT FieldNumber = ROW_NUMBER() OVER (ORDER BY t.N)

  ,FieldString = SUBSTRING(@pString, (t.N+2) ,CHARINDEX('>''',@pString,t.N)-(t.N+2))

  FROM cteTally t

  WHERE t.N <= LEN(@pString)

  AND SUBSTRING(@pString,t.N,2) = '''<'

  ) --======== Repivot the string elements back to a horizontal row using a CrossTab.

  SELECT FieldName = MAX(CASE WHEN FieldNumber = 1 THEN FieldString ELSE '' END)

  ,ChangedFrom = MAX(CASE WHEN FieldNumber = 2 THEN FieldString ELSE '' END)

  ,ChangedTo = MAX(CASE WHEN FieldNumber = 3 THEN FieldString ELSE '' END)

  ,ChangedBy = MAX(CASE WHEN FieldNumber = 4 THEN FieldString ELSE '' END)

  ,ChangedDate = MAX(CASE WHEN FieldNumber = 5 THEN FieldString ELSE '' END)

  FROM cteSplit

  ;

  Then, call it to "normalize" your entire table like this...

  SELECT split.FieldName

  ,split.ChangedFrom

  ,split.ChangedTo

  ,split.ChangedBy

  ,split.ChangedDate

  FROM dbo.YourTable yt

  CROSS APPLY dbo.TokenizeAuditString(yt.TheColumn)split

  ;

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)