CODEWITHSUNDEEP
sql-servert-sql
codeepic
codeepic

Reputation: 4102

Split a database table column value of type VARCHAR into multiple values using a delimiter?

Can you split a database table column value of type VARCHAR into multiple values using a delimiter?

So when fetching data I would like to split values like text|another|other using pipe | as a delimiter.

We are using stored procedures and T-SQL, I have somewhat basic understanding of SQL, but my understanding is that using STRING_SPLIT https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 ths should be possible.

Can anyone assist me with more details?

Upvotes: 0

Views: 896

Answers (4)

Praveen ND
Praveen ND

Reputation: 560

Please check the below answer. This might help :

DECLARE @Table TABLE (ID int, SampleData Varchar(100))

INSERT @Table
(ID,SampleData)
VALUES
(1, 'text|another|other'),
(2, 'sample|data|again'),
(3, 'this|should|help')


 SELECT 
     A.ID AS ID,  
     Split.a.value('.', 'VARCHAR(100)') AS SampleData  
 FROM  (
        SELECT 
            ID,  
            CAST ('<M>' + REPLACE(SampleData, '|', '</M><M>') + '</M>' AS XML) AS SampleData  
        FROM  
            @Table A
         )  AS A 
    CROSS APPLY SampleData.nodes ('/M') AS Split(a)

The Output:-

ID  SampleData
1   text
1   another
1   other
2   sample
2   data
2   again
3   this
3   should
3   help

Upvotes: 1

Jirajha
Jirajha

Reputation: 493

The short answer is: You can do it.

The longer answer is: You shouldn't do it, because it violates the principle of Database Normalization. Anything up to NF3 is the (arguably) bare minimum, which you should stick to.

If the data enters your database from an external source, Transform it in your ETL process before it actually enters your database.

Your database should then store the data in relational form, and other transformations to fit your frontend should be done upon retrival to make it fit your consuming applications. You can read up on the ANSI-SPARC Architecture if you so desire.

Upvotes: 1

Richard Matriche
Richard Matriche

Reputation: 87

Well as Panagiotis Kanavos said as a comment, the best and easiest would be to this in the 'Transform' part of your ETL (Extract, Transform, Load). When you do this, you don't even have to deal with SQL : since you are on SQL-SERVER i assume you use SSIS. You can call an external C# or .Net script to do all the work.

To answer your question, yes SQL provides a lot of builtin function to deals with string : you can use substr() + charindex(), or String_split or even write your own

Microsoft doc about String Functions : https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql?view=sql-server-ver15

Upvotes: 2

squillman
squillman

Reputation: 13641

It is definitely possible, and pretty straight-forward with STRING_SPLIT. Even before SQL Server 2017 when STRING_SPLIT was introduced it was possible, just not as pretty.

An example with your string would be something like this:

DECLARE @t TABLE (ID INT, StringVal VARCHAR(1000));

INSERT @t
VALUES (1, 'text|another|other');

SELECT *
FROM @t
CROSS APPLY STRING_SPLIT(StringVal, '|');

The results of this are 

ID| StringVal          | value
--------------------------------
1 | text|another|other | text
1 | text|another|other | another
1 | text|another|other | other

So to answer your question, yes it is definitely possible. However, you should certainly take the comment discussion into consideration.

Upvotes: 2

Related Questions