Efficient Way to String Split Using Cte

Efficient way to string split using CTE

You seem dead set on using a CTE, so try this:

DECLARE @YourTable table (RowID int, Layout varchar(200))
INSERT @YourTable VALUES (1,'hello,world,welcome,to,tsql')
INSERT @YourTable VALUES (2,'welcome,to,stackoverflow')

;WITH SplitSting AS
(
    SELECT
        RowID,LEFT(Layout,CHARINDEX(',',Layout)-1) AS Part
            ,RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) AS Remainder
        FROM @YourTable
        WHERE Layout IS NOT NULL AND CHARINDEX(',',Layout)>0
    UNION ALL
    SELECT
        RowID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT
        RowID,Remainder,null
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT * FROM SplitSting ORDER BY RowID

OUTPUT:

RowID       Part                   
----------- -----------------------
1           hello                  
1           world                  
1           welcome                
1           to                     
1           tsql                   
2           welcome                
2           to                     
2           stackoverflow          

(8 row(s) affected)

here is an excellent article on splitting strings in SQL Server: "Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

EDIT here's another version (but you need a numbers table) returns same results as above:

;WITH SplitValues AS
(
    SELECT
        RowID,ListValue
        FROM (SELECT
                  RowID, LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT RowID, ',' + Layout + ',' AS List2
                           FROM @YourTable
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = ','
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''
)
SELECT * FROM SplitValues

see here for a numbers table: What is the best way to create and populate a numbers table?

How do I split a delimited string so I can access individual items?

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

You can use this simple logic:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

Split a string with varying factors in SQL Server

This is tagged with [sql-server-2012] - what a pity... With v2016+ you could call for STRING_SPLIT or even JSON methods... The following is a rather hacky approach but works - at least with your provided test data...

Create a mockup-table (please do this yourself the next time).

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(100));
INSERT INTO @tbl VALUES
 ('["2","3","4"]')
,('["1","2","3"]')
,('[]')
,('["Select option B","Select option C","Select option D"]')
,('["Moderate","Heavy","Heavy, Big & Abnormal"]');

--This is the query:

SELECT t.ID
      --,t.YourString 
      ,C.Separated.value('text()[1]','nvarchar(max)') AS Parted
FROM @tbl t
CROSS APPLY(SELECT REPLACE(REPLACE(REPLACE(YourString,'"','"'),'["',''),'"]','')) A(replaced)
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT A.replaced [*] FOR XML PATH('')),'","','</x><x>') + '</x>' AS XML)) B(casted)
CROSS APPLY B.casted.nodes('/x') C(Separated);

The idea in short:

First of all I use multiple REPLACE() to clean and harmonise your data. The second CROSS APPLY will then use XML to split up your strings by replacing each comma together with the quotes! with XML tags. Thus we can prevent splitting at internal commas. But before, we have to use FOR XML on the orginal string, to allow characters such as the & in Big & Abnormal. The rest ist rather easy XPath/XQuery.

The result

+----+-----------------------+
| ID | Parted                |
+----+-----------------------+
| 1  | 2                     |
+----+-----------------------+
| 1  | 3                     |
+----+-----------------------+
| 1  | 4                     |
+----+-----------------------+
| 2  | 1                     |
+----+-----------------------+
| 2  | 2                     |
+----+-----------------------+
| 2  | 3                     |
+----+-----------------------+
| 3  | []                    |
+----+-----------------------+
| 4  | Select option B       |
+----+-----------------------+
| 4  | Select option C       |
+----+-----------------------+
| 4  | Select option D       |
+----+-----------------------+
| 5  | Moderate              |
+----+-----------------------+
| 5  | Heavy                 |
+----+-----------------------+
| 5  | Heavy, Big & Abnormal |
+----+-----------------------+

How to split string to characters and then enclose every character with a tag

You can do this efficiently using a cross join and a tally/numbers table. This query usess a tally-table on the fly but a permanent solution would have a static table with 1-n values.

select Concat('<span>',Substring(s,n,1),'</span>')
from 
(values('34A450-45'))v(s)
cross apply (
    select n from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))n(n)
    where n.n<=Len(s)
)n

Split String into given length but do not split word

Assuming you have a table of addresses, I'd use a recursive CTE.

On each iteration, find the last possible space to break on, then start the next iteration for the character after the space.

take 31 characters
reverse them
find the position of the first space

Extra care to be taken for corner cases:

The remaining string to be searched is less than 30 characters
The current string being searched has no space in the first 31 characters

Using the following test data...

CREATE TABLE test (
  address   VARCHAR(MAX)
);

INSERT INTO
  test
VALUES
  ('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTown Upper1Location Another5 NewYork'),
  ('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTownxx Upper1LocationUpper1LocationUpper1Location Another5 NewYork'),
  ('216 Apartment123 AreaArea SampleWord1 Word2 MiddleTownxx Upper1LocationUpper1LocationUpper1Location Another5 NewYork x')

;

Using the following CTE...

DECLARE @chars BIGINT = 30;

WITH
  parts AS
(
  SELECT
    address,
    LEN(address)        AS length,
    CAST(0 AS BIGINT)   AS last_space,
    CAST(1 AS BIGINT)   AS next,
    address             AS fragment
  FROM
    test

  UNION ALL

  SELECT
    parts.address,
    parts.length,
    last_space.pos,
    parts.next + COALESCE(last_space.pos, @chars),
    SUBSTRING(parts.address, parts.next, COALESCE(last_space.pos - 1, @chars))
  FROM
    parts
  CROSS APPLY
  (
    SELECT
      @chars + 2
      -
      NULLIF(
        CHARINDEX(
          ' ',
          REVERSE(
            SUBSTRING(
              parts.address + ' ',
              parts.next,
              @chars + 1
            )
          )
        )
        , 0
      )
  )
    last_space(pos)
  WHERE
    parts.next <= parts.length
)

SELECT
  *, len(fragment) AS chars
FROM
  parts
WHERE
  next > 1
ORDER BY
  address,
  next

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=acd11f2bc73e5036bd82498ecf14b08f

How to split a comma-separated value to columns

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END

    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END

    RETURN
END

How to split a comma delimited string using ONLY a select statement?

Query

 Declare @String nvarchar(500) = 'This,Is,A,Sentence';

SELECT  Split.a.value('.', 'VARCHAR(100)') Words
FROM (
      SELECT Cast ('<X>' 
                   + Replace(@String, ',', '</X><X>') 
                   + '</X>' AS XML) AS Data
     ) AS t CROSS APPLY Data.nodes ('/X') AS Split(a);

Result Set:

╔══════════╗
║  Words   ║
╠══════════╣
║ This     ║
║ Is       ║
║ A        ║
║ Sentence ║
╚══════════╝

T-SQL split string

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

Split function equivalent in T-SQL?

Here is somewhat old-fashioned solution:

/*
    Splits string into parts delimitered with specified character.
*/
CREATE FUNCTION [dbo].[SDF_SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
            @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
        set @iStart = 2
        insert into @tParts
        values( null )
    end
    else 
        set @iStart = 1
    while 1=1
    begin
        set @iPos = charindex( @cDelimiter, @sString, @iStart )
        if @iPos = 0
            set @iPos = len( @sString )+1
        if @iPos - @iStart > 0          
            insert into @tParts
            values  ( substring( @sString, @iStart, @iPos-@iStart ))
        else
            insert into @tParts
            values( null )
        set @iStart = @iPos+1
        if @iStart > len( @sString ) 
            break
    end
    RETURN

END

In SQL Server 2008 you can achieve the same with .NET code. Maybe it would work faster, but definitely this approach is easier to manage.

Is this split function efficient, or is there better one?

For some discussions on different string splitting methods and their efficiency, I tend to try to get people to stop trying to do this in T-SQL. You can spend hours fighting with inefficient functions to try and squeeze a few extra microseconds out of them, but it's an exercise in futility. T-SQL is inherently slow at this task and it's much better to go outside of T-SQL - either by using CLR (2005) or Table-Valued Parameters (TVPs) (2008+). I recently published a three-part series on this that is likely worth a read, and I suspect you'll come to the same conclusions I did (CLR is good, TVPs are better, and all T-SQL methods just look silly in comparison):

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

Also please mention how did you measure it's efficiency.

Well, you can do what I did in those articles, select SYSDATETIME() before and after you run each test, and then calculate the difference. You can also log to a table before and after each test, or use Profiler to capture , or surround your test with:

SET STATISTICS TIME ON;

PRINT 'Test 1';

-- do test 1 here

PRINT 'Test 2';

-- do test 2 here

SET STATISTICS TIME OFF;

You'll get output in the messages pane like:

Test 1

SQL Server execution times:
  CPU time: 247 ms, elapsed time: 345 ms

Test 2

SQL Server execution times:
  CPU time: 332 ms, elapsed time: 421 ms

Finally, you can use our free tool, SQL Sentry Plan Explorer. (Disclaimer: I work for SQL Sentry.)

You can feed any query into Plan Explorer, generate an actual plan, and in addition to a graphical plan that is much more readable than the showplan put out my Management Studio, you also get runtime metrics such as duration, CPU and reads. So you can run two queries and compare them side by side without doing any of the above:

Sample Image

Efficient Way to String Split Using Cte