Edit string column in SQL - remove sections between separators

Question

I have a string column in my table that contains 'Character-separated' data such as this:

"Value|Data|4|Z|11/06/2012"

This data is fed into a 'parser' and deserialised into a particular object. (The details of this aren't relevant and can't be changed)

The structure of my object has changed and now I would like to get rid of some of the 'sections' of data

So I want the previous value to turn into this

"Value|Data|11/06/2012"

I was hoping I might be able to get some help on how I would go about doing this in T-SQL.

The data always has the same number of sections, 'n' and I will want to remove the same sections for all rows , 'n-x and 'n-y'

So far I know I need an update statement to update my column value. I've found various ways of splitting a string but I'm struggling to apply it to my scenario.

In C# I would do

string RemoveSecitons(string value)
{
    string[] bits = string.split(value,'|');

    List<string> wantedBits = new List<string>();


    for(var i = 0; i < bits.Length; i++)
    {
        if ( i==2 || i==3) // position of sections I no longer want
        {
            continue;
        }
        wantedBits.Add(bits[i]);
    }

    return string.Join(wantedBits,'|');

}

But how I would do this in SQL I'm not sure where to start. Any help here would be appreciated

Thanks

Ps. I need to run this SQL on SQL Server 2012

Edit: It looks like parsing to xml in some manner could be a popular answer here, however I can't guarantee my string won't have characters such as '<' or '&'

Answer 1

Using NGrams8K you can easily write a nasty fast customized splitter. The logic here is based on DelimitedSplit8K. This will likely outperform even the C# code you posted.

DECLARE @string VARCHAR(8000) = '"Value|Data|4|Z|11/06/2012"',
        @delim  CHAR(1)       = '|';

SELECT newString = 
(
  SELECT SUBSTRING(
           @string, split.pos+1,
           ISNULL(NULLIF(CHARINDEX(@delim,@string,split.pos+1),0),8000)-split.pos)
  FROM
  (
    SELECT ROW_NUMBER() OVER (ORDER BY d.Pos), d.Pos
    FROM
    (
      SELECT 0 UNION ALL
      SELECT ng.position 
      FROM   samd.ngrams8k(@string,1) AS ng
      WHERE  ng.token = @delim
    ) AS d(Pos)
  ) AS split(ItemNumber,Pos)
  WHERE split.ItemNumber IN (1,2,5)
  ORDER BY split.ItemNumber
  FOR XML PATH('')
);

Returns:

newString
----------------------------
"Value|Data|11/06/2012"

Answer 2

You might try some XQuery:

DECLARE @s VARCHAR(100)='Value|Data|4|Z|11/06/2012';

SELECT CAST('<x>' + REPLACE(@s,'|','</x><x>') + '</x>' AS XML)
       .value('concat(/x[1],"|",/x[2],"|",/x[5])','nvarchar(max)');

In short: The value is trasformed to XML by some string replacements. Then we use the XQuery-concat to bind the first, the second and the fifth element together again.

This version is a bit less efficient but safe with forbidden characters:

SELECT CAST('<x>' + REPLACE((SELECT @s AS [*] FOR XML PATH('')),'|','</x><x>') + '</x>' AS XML)
       .value('concat(/x[1],"|",/x[2],"|",/x[5])','nvarchar(max)')

Answer 3

Just to add a non-xml option for fun:

Edit and Caveat - In case anyone tries this for a different solution and doesn't read the comments...

HABO rightly noted that this is easily broken if any of the columns have a period (".") in them. PARSENAME is dependent on a 4 part naming structure and will return NULL if that is exceeded. This solution will also break if any values ever contain another pipe ("|") or another delimited column is added - the substring in my answer is specifically there as a workaround for the dependency on the 4 part naming. If you are trying to use this solution on, say, a variable with 7 delimited columns, it would need to be reworked or scrapped in favor of one of the other answers here.

DECLARE 
    @a VARCHAR(100)= 'Value|Data|4|Z|11/06/2012'


SELECT 
    PARSENAME(REPLACE(SUBSTRING(@a,0,LEN(@a)-CHARINDEX('|',REVERSE(@a))+1),'|','.'),4)+'|'+
    PARSENAME(REPLACE(SUBSTRING(@a,0,LEN(@a)-CHARINDEX('|',REVERSE(@a))+1),'|','.'),3)+'|'+
    SUBSTRING(@a,LEN(@a)-CHARINDEX('|',REVERSE(@a))+2,LEN(@a))

Answer 4

Not the most elegant way, but works:

SELECT SUBSTRING(@str,1, CHARINDEX('|',@str,CHARINDEX('|',@str,1)+1)-1)
   + SUBSTRING(@str, CHARINDEX('|',@str,CHARINDEX('|',@str,CHARINDEX('|',@str,CHARINDEX('|',@str,1)+1)+1)+1), LEN(@str))


----------------------
Value|Data|11/06/2012

Answer 5

Here is a quick way to do it.

CREATE FUNCTION [dbo].StringSplitXML
(
    @String  VARCHAR(MAX), @Separator CHAR(1)
)
RETURNS @RESULT TABLE(id int identity(1,1),Value VARCHAR(MAX))
AS
BEGIN    
 DECLARE @XML XML
 SET @XML = CAST(
    ('<i>' + REPLACE(@String, @Separator, '</i><i>') + '</i>')
    AS XML)

 INSERT INTO @RESULT
 SELECT t.i.value('.', 'VARCHAR(MAX)') 
 FROM @XML.nodes('i') AS t(i)
 WHERE t.i.value('.', 'VARCHAR(MAX)') <> ''

 RETURN
END
GO
SELECT * FROM dbo.StringSplitXML( 'Value|Data|4|Z|11/06/2012','|')
WHERE id not in (3,4)

Note that using a UDF will slow things down, so this solution should be considered only if you have a reasonably small data set to work with.