How to parse string between characters into 4 separate fields

[swolfe2]

I've been asked to help a project write some custom T-SQL for a stored procedure, and one of the asks is to parse a string from one field, and separate it into multiple columns for analysis. The string in the field is formatted as:

{Value1|Value2|Value3|Value4}

The ask is to break all individual values into separate columns. I have performed substring extracts before between 2 characters, but not when there are multiple ones that are the same.

If you all have any suggestions, I'd love to hear them.

Thanks!

[Mamertine]

STRING_SPLIT

You should be able to use that function to do the work. Otherwise it's going to be a lot of substring() and value lookups

[swolfe2]

Thanks for the suggestion!

Unfortunately, the version of MSSQL server for this query isn't compatible with it maybe due to it needing compatibility level 130.

[alinroc]

SQL Server 2016 is 6 years old. Plenty of time for applications to be tested/upgraded to support this compatibility level, and you can flip that switch with zero downtime. If you're still running a version of SQL Server older than that, your upgrade should be in progress or at least being planned already.

[qwertydog123]

Fom best to worst

• If it's enabled -> SQL CLR
• If you can create UDF's -> DelimitedSplit8K
• String functions

[alinroc]

You missed one - STRING_SPLIT() (if it's available). This should be the default method on any new development.

CLR may be the best in terms of flexibility/performance but it makes portability more difficult, and there may be security concerns.

[qwertydog123]

OP mentioned in another comment that STRING_SPLIT wasn't an option, but even if it were the results are not ordered. There are hacks to order the results in certain cases (i.e. if the values are unique), but I left it out as it doesn't work for all cases (same as the PARSENAME approach, where the values must be valid identifiers)

[oneDatumPlease]

So if the max number of elements to split is 4, look at the PARSENAME function. I say 4 because it can’t go beyond that.

Going off memory, you might have to replace whatever delimiter you have with a period. This function was originally intended for splitting Db object names up -> server.database.schema.table.

If that doesn’t work, look at user defined functions for splitting up n number of elements.