STRING_SPLIT: Splitting strings into multiple rows using SQL Server using a delimiter or separator
HTML-код
- Опубликовано: 15 сен 2024
- In this video, we will be looking at how to use a delimiter or separator to split a string into multiple rows.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
In this video, I'm showing you how to split a string into multiple rows using SQL Server using a delimiter or separator.
If you're working with a large string that you want to split into multiple rows, then this technique is perfect for you. By splitting the string into multiple rows, you can more easily handle and analyze the data. Plus, this technique is easy to apply using SQL Server, so you'll be able to get started quickly!
If you have a string such as "Jacksonville,Tampa,Orlando,Miami,Tallahassee", how can you separate it into 5 different rows? In this video, we'll have a look at STRING_SPLIT (which you can use from SQL server 2016 onwards).
It uses 2 arguments - the string to be split, and the one character delimiter or separator.
It returns one column which is called "value". If you are using an Azure database, then you can use a third argument to return a second column called "ordinal", which numbers the output.
Here is an example of how you would use it:
DECLARE @strText AS VARCHAR(300) = 'Jacksonville,Tampa,Orlando,Miami,Tallahassee'
SELECT @strText as MyText
SELECT value as TheResults FROM STRING_SPLIT(@strText, ',')
You can also use CROSS APPLY to use it in conjunction with another table or view:
SELECT ProductDescriptionID, trim(Value) as Sentence
FROM [Production].[ProductDescription]
CROSS APPLY
STRING_SPLIT(Description, '.')
In Azure SQL, you can add the third argument, such as:
STRING_SPLIT(Description, '.', 1)
love this video - easy & straight forward
clear english is an asset here :D
Thank you very much! Very informative and saved my time.
I don't have the split function, I am using the 2012 version, any suggestions on how to split 4 strings in one column into 4.
Hi Solomong. Please have a look at stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 . Phillip
Help but if we want to split based on length,60 characters in each row
Hi Prith. Thank you for your suggestion. That's a good idea - I'll add it to my "to do" list. Phillip
How did the cross-apply work here as we have not provided any where condition. Also, I believe if we don’t apply the where condition then it connect every row of first table with another, something like cross join. Can you pls help me to understand? Thanks
Hi Vivek. Cross Apply is not the same as Cross Join. Cross Apply looks at each row, and applies the function (in this case, STRING_SPLIT) for each row. If the function gives a NULL, then the row is withheld (an OUTER APPLY would retain that row). Phillip
Thank you very much
Can we have a video on how to do row versioning?
I am using 2016 version, SPLIT_STRING is not available. Can we do without SPLIT_STRING?
Hi Uday. You can - but it's not easy. Have a look at this webpage for details: sqlperformance.com/2021/09/t-sql-queries/split-strings . Phillip
What if B.C.C. is present ? And we don't want to split B.C.C.
Please Provide solution
Dear Nikhil,
Thank you for your question.
Very tricky. Instead of using the delimiter '.' (no space), you should use the delimiter '. ' (with a space). This means that 'B.B.C.' would remain as is. However, you can't use STRING_SPLIT with a separator of more than 1 character.
This means you should use REPLACE to replace '. ' (with a space) by a character which is cannot possibly be in the string. I would use `, but for readability, in the next few examples, I will use +. You could use:
DECLARE @var varchar(80) = 'For true trails addicts. An extremely durable B.B.C. bike.'
SELECT *
FROM string_split(replace(@var,'. ','+'),'+')
This results in:
For true trails addicts
An extremely durable B.B.C
bike.
Alternately, you should change B.B.C. to the +, and then change it back - for example:
DECLARE @var varchar(80) = 'For true trails addicts. An extremely durable B.B.C. bike.'
SELECT replace([value],'+','B.B.C') as [value]
FROM string_split(replace(@var,'B.B.C.','+'),'.')
This results in:
For true trails addicts
An extremely durable B.B.C bike
I hope this helps.
Regards,
Phillip
what about in Postgresql?
Hi Sushugowda. This channel is about Microsoft SQL Server, not PostgreSQL. Phillip
nice!
Hi Suppose i am having a word like "Server" As input.
My output should be in differrnr lines.
Output :
S
E
R
V
E
R
This is simple in python. But not sure how to do it in sql. Repeated sql question in interview
Thanks in advance
Hi Dinesh. Please have a look at stackoverflow.com/questions/8517816/t-sql-split-word-into-characters . It's a very good question - I'll add it to my list of videos to create. Phillip
I have a question: How to Separate a word like this "Howtoseparatethiswordinonecolum" and the result is "this How to separate this word in one colum"
I don't know that you can. You would need the computer to know where each word starts without any clues. Phillip
@@SQLServer101 Thanks for reply dude, I am looking for solution/query on this. On how to query the space between the capital letters, thats why I saw your tutorials dudes and asking it possible you know the query, thanks for answer