T-SQL splits a string using the specified delimiter (split string)

For example, there is a table, we need a statement like Select Otherid, splitdata WHERE someid = ' abcdef-....... ', and then you can return the split into separate rows.

Original table:

| Someid | Otherid | Data

+----------------+-------------+-------------------

| ABCdef-..... | Cdef123-... | 18,20,22

| ABCdef-..... | 4554a24-... | 17,19

| 987654-..... | 12324A2-... | 13,19,20

Expected Result:

| Otherid | Splitdata

+-------------+-------------------

| Cdef123-... | 18

| Cdef123-... | 20

| Cdef123-... | 22

| 4554a24-... | 17

| 4554a24-... | 19

The introduction of the split-string function String_split (detailed reference to MSDN) in SQL Server 2016 makes it easy to implement.

Select Otherid, Splitdata  from yourtable  Cross ' , ') Cs

Before SQL Server 2016, you had to add a custom function that was implemented in two ways.

1. XML parsing--easier to use for strings that can be converted to XML (no special characters or special characters can be replaced)

CREATE FUNCTION [dbo].[splitstring](   @List       NVARCHAR(MAX),   @Delimiter  NVARCHAR(255))RETURNS TABLE withSCHEMABINDING as   RETURN   (       SELECTItem=Y.i.value ('(./text ()) [1]','nvarchar (4000)')       from      (        SELECTX= CONVERT(XML,'<i>'          + REPLACE(@List,@Delimiter,'</i><i>')          + '</i>'). Query ('.')      )  asA CrossAPPLY X.nodes ('I') asy (i));

2. Recursive method

Create function [dbo].[splitstring](@input Varchar(Max),@Splitter Varchar( About))returns Table  asReturn   withTMP (DataItem, List, first) as   (             Select @input,@input,1  --First item ignored, set to get the type right     Union  All     Select  Left(List,CHARINDEX(@Splitter, List+@Splitter)-1),                                    STUFF(List,1,CHARINDEX(@Splitter, List+@Splitter),"'),                                    0              fromtmpwhereList<> "'   )     SelectDataItem fromTmpwhereFirst=0

How to use:

Select Otherid, Splitdata  from yourtable  Cross ' , ') Cs

T-SQL splits a string using the specified delimiter (split string)