Xavier W. Xavier W. - 7 months ago 7
SQL Question

Split a varchar in a select request

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

Answer

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)