Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Returning part of a string Expand / Collapse
Author
Message
Posted Saturday, October 19, 2013 5:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 8:54 AM
Points: 32, Visits: 77
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
Post #1506421
Posted Saturday, October 19, 2013 9:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506430
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse