Split a varchar in a select request

Question

I have a column call Title, and I need to split it in 3 with the following delimiter : <BR>. If the title is empty, I have to set the three column as empty

I use a temporary table that was filled with multiple row and I want to split the Title during the insert in the temporary table.

Here is a snapshot of my table :

CREATE TABLE #tempResults (
        [...]
        EMM_TITLE nvarchar(255),
        TITLE_LINE_1 nvarchar(255), 
        TITLE_LINE_2 nvarchar(255), 
        TITLE_LINE_3 nvarchar(255))

Here is my insert request. The line EMM_TITLE is retrieve from a database

INSERT INTO [#tempResults]
        SELECT 
         [...]
         'Fooooo<BR>Fooooo2<BR>Foooooo3'
         '' -- Expect : Fooooo
         '' -- Expect : Fooooo2
         '' -- Expect : Foooooo3

How is it possible for me to split the columns in three and set empty if the TITLE is empty ?

I find posts about similar issue, but wasn't able to apply them to my case : Link

Do you mean that you want to update the title to be `TITLE_LINE_1
TITLE_LINE_2
TITLE_LINE_3
` ? — CompanyDroneFromSector7G, Apr 21 '16 at 14:38
...or do you mean, that it was it looks like now and you want to split it into the three columns? — CompanyDroneFromSector7G, Apr 21 '16 at 14:39
No, I want to set the first part of `TITLE` in `TITLE_LINE_1`, the second in `TITLE_LINE_2`, the rest in `TITLE_LINE_3` — Xavier W., Apr 21 '16 at 14:40

CompanyDroneFromSector7G · Accepted Answer · 2016-04-21T15:00:16.880

This Split function might help for a start:

CREATE FUNCTION [dbo].[Split]
(
    @RowData nvarchar(2000),
    @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data nvarchar(100)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+len(@SplitOn),len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

GO

You can use it like:

SELECT * FROM dbo.Split('Fooooo<BR>Fooooo2<BR>Foooooo3','<BR>')

...or even better, use it in conjunction with the following function:

CREATE FUNCTION Word 
(
    @Input  nvarchar(max),
    @Delim  nvarchar(10),
    @Item   int
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @result nvarchar(max)

    SELECT  @result = Data
    FROM    dbo.Split(@Input, @Delim)
    WHERE   Id = @Item

    RETURN @Result

END

For example:

UPDATE  #tempTable
SET     TITLE_LINE_1 =  dbo.Word(EMM_TITLE ,'<BR>',1),
        TITLE_LINE_2 =  dbo.Word(EMM_TITLE ,'<BR>',2),
        TITLE_LINE_3 =  dbo.Word(EMM_TITLE ,'<BR>',3)

It would be perfect if there is a third parameter saying which part do you want to return — Xavier W., Apr 21 '16 at 14:54
This is pretty good but you can do this without using a loop. — Sean Lange, Apr 21 '16 at 16:26

score 0 · Answer 2 · answered Apr 21 '16 at 16:33

Here is an alternative method of doing this that doesn't require a loop. It will be much faster than using a loop inside a multi statement table valued function. This technique borrows VERY heavily from the fine work of Jeff Moden and the community at sql server central. Here is the article I am referring to. http://www.sqlservercentral.com/articles/Tally+Table/72993/

if OBJECT_ID('DelimitedSplit8K_LongDelimiter') is not null
    drop function DelimitedSplit8K_LongDelimiter
GO

CREATE FUNCTION [dbo].[DelimitedSplit8K_LongDelimiter]
(
    @pString VARCHAR(8000)
    , @pDelimiter VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...


WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM E4
    ),
    cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N + 1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString, t.N, DATALENGTH(@pDelimiter)) = @pDelimiter OR t.N = 0) 
                )

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
select ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1)
    , Item = SUBSTRING(@pString, Case when s.N1 = 1 then 1 else s.N1 + (DATALENGTH(@pDelimiter) - 1) end, 
        ISNULL(
                NULLIF(
                    CHARINDEX(@pDelimiter, @pString, s.N1 )
                    ,0)
                    - Case when s.N1 = 1 then 1 else s.N1 + (DATALENGTH(@pDelimiter) - 1) end,8000)
    )

Split a varchar in a select request

2 Answers2