CODEWITHSUNDEEP
sql-servert-sqlsplit
sykfarhan
sykfarhan

Reputation: 73

SQL Server: STRING_SPLIT() result in a computed column

I couldn't find good documentation on this, but I have a table that has a long string as one of it's columns. Here's some example data of what it looks like:

Hello:Goodbye:Apple:Orange

Example:Seagull:Cake:Chocolate

I would like to create a new computed column using the STRING_SPLIT() function to return the third value in the string table.

What is the proper syntax to achieve this?

Upvotes: 1

Views: 752

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

You shouldn't be storing data like that in the first place. This points to a potentially serious database design problem. BUT you could convert this string into JSON by replacing : with ",", surround it with [" and "] and retrieve the third array element , eg :

declare @value nvarchar(200)='Example:Seagull:Cake:Chocolate'
select json_value('["' + replace(@value,':','","' )+ '"]','$[2]')

The string manipulations convert the string value to :

["Example","Seagull","Cake","Chocolate"]

After that, JSON_VALUE parses the JSON string and retrieves the 3rd item in the array using a JSON PATH expression.

Needless to say, this will be slow and can't take advantage of indexing. If those values are meant to be read or written individually, they should be stored in separate columns. They'll probably take less space than one long string.

If you have a lot of optional fields but only a subset contain values at any time, you could use sparse columns. This way you could have thousands of rows, only a few of which would contain data at any time

Upvotes: 1

Wes H
Wes H

Reputation: 4439

At this time your answer is not possible.

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string.

STRING_SPLIT reference

There is no way to guarantee which item was the third item in the list using string_split and the order may change without warning.

If you're willing to build your own, I'd recommend reading up on the work done by
Brent Ozar and Jeff Moden.

Upvotes: 2

Related Questions