String Manipulation

Question

question

blessenmk asked Feb 15, '22

String Manipulation

I have a column consisting of strings of the like abc_defg_ghi_jklmn etc. The number of characters after and before the delimiter is dynamic. For this example, I wish to split the string into 4 columns.

Output (in this case) would look like -

col1 : abc

col2 : defg

col3 : ghi

col4: jklmn

I was able to achieve this but the query is exceptionally long with combinations of substring and charindex, and confusing to others who read it.

Is there a better way to go about this? Please recommend, and the query to help understand the solution. I'm using MS SQL Server Management Studio 17.

string-splitting delimiter

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2022-02-15T06:31:31Z

anthony.green answered Feb 15, '22

What edition of SQL are you using?

If you connect to your server then run a “SELECT @@VERSION” it will detail SQL 2012/2014/2016/2017/2019 etc.

If you are on SQL 2016 or above you could use the STRING_SPlLT function instead, note that the split order could be random so don’t expect the same order each time split.

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

The alternative would be some form of dynamic pivot and using DelimitedSplit8K as no point reinventing the wheel.

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2022-02-15T18:23:35Z

blessenmk answered Feb 15, '22

Thanks, Anthony. I'm using Microsoft SQL Azure (RTM) - 12.0.2000.8.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2022-02-15T18:27:27Z

blessenmk answered Feb 15, '22

The below example (1) works, but there would be cases wherein the column would have a combination of string and numbers.

Example 1 :

declare @object_name char(15) = '172.217.167.110'

select

PARSENAME(@object_name, 4) AS [4],

PARSENAME(@object_name, 3) AS [3],

PARSENAME(@object_name, 2) AS [2],

PARSENAME(@object_name, 1) AS [1]

Example 2 :

object_name char(40) = 'tom.holland.172.217.167.110'. I get NULLS for all select statements. Tried datatypes varchar and nvarchar.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2022-02-17T16:34:17Z

anthony.green answered Feb 17, '22

So if your using Azure you can use STRING_SPLIT, then you can use a dynamic pivot to convert it back to columns instead of rows.

Otherwise it’s a nasty huge complicated beast like you have already.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question