Splitting Tabbed data held in SQL Field

Transact-SQL
1

If I have a string, tab delimited, how can I select that string as columns

CREATE TABLE #TABTest

(

Importeddata nvarchar(MAX) NOT NULL

);

INSERT INTO #TABTest
(Importeddata)
VALUES
('TEST1 Ref01 TEST1 TEST1 TEST1 TEST1 TEST1 TEST1 11-11-11 11111111 SomePlace 0.01 01/01/1900 000000 00-00-00 00000000')

INSERT INTO #TABTest
(Importeddata)
VALUES
('TEST2 Ref02 TEST2 TEST2 TEST2 TEST2 TEST2 TEST2 22-22-22 22222222 SomePlace 0.02 01/01/1900 000000 00-00-00 00000000')

DROP TABLE #TABTest

I'm looking to get

Col1 Col2 ETC
TEST1 Ref01
TEST2 Ref02

2

To split the data you can use STRING_SPLIT - and then to put it into a single row you can use STRING_AGG. Unless you are on SQL Server 2022 then you won't be able to guarantee the order of the fields - you would need a custom string splitter that guarantees the order (look up DelimitedSplit8K).

With that said - why do you want these values in separate columns? The better approach is to split it into rows so you can more easily identify each value and perform comparisons between each value in the string.

3

Hi,

The story behind this is files are read from a legacy webApp and 'translated' into a usable format, before being inserted into the application SQL tables. This is currently done by lengthy vb.net code. I was looking for a quicker more streamlined way of doing this.

I have looked at split_string but cannot get anything back apart from a null value. I don't need all the data from the strings, just a few selected bits to return back tot he calling application.

4

To be able to use any string splitter - you need to identify the delimiter. It sounds like you are not using the correct value as the delimiter.

You sample data is using a space as the delimiter.

Either way, STRING_SPLIT on versions less than 2022 won't return the index. If you just need specific 'fields' returned that won't work. You would need something like DelimitedSplit8K (Tally OH! An Improved SQL 8K “CSV Splitter” Function – SQLServerCentral)

5

Hi its unclear in my sample, but its tab delimited. I'll have a look at DelimitedSplit8k before giving up on this one .

Nevertheless, thank you for your help on this.