CODEWITHSUNDEEP
sql-servert-sql
Anuj Tamrakar
Anuj Tamrakar

Reputation: 271

How can I split string based on combination of characters?

I have this string: 'Hello my Name is #!Jospeh#!'. I want my Output to be Jospeh.

Lets say if i have this string: 'Hello my Name is #!Joseph#! #!King#!'. I want my Output to me 'Joseph' and 'King'

I have created a function:

ALTER FUNCTION [dbo].[TfSplitTemplateVariable]
    (
      @String NVARCHAR(4000) ,
      @Delimiter NCHAR(2)
    )
RETURNS TABLE
AS
RETURN
    (
    WITH    Split ( stpos, endpos )
              AS ( SELECT   0 AS stpos ,
                            CHARINDEX(@Delimiter, @String) AS endpos
                   UNION ALL
                   SELECT   endpos + 1 ,
                            CHARINDEX(@Delimiter, @String, endpos + 1)
                   FROM     Split
                   WHERE    endpos > 0
                 )
    SELECT  'Id' = ROW_NUMBER() OVER ( ORDER BY ( SELECT    1
                                                ) ) ,
            'Data' = SUBSTRING(@String, stpos,
                               COALESCE(NULLIF(endpos, 0), LEN(@String) + 1)
                               - stpos)
    FROM    Split
)
GO

And when i run this:

SELECT tstv.* FROM dbo.TfSplitTemplateVariable('Hello my Name is #!Jospeh#!','#!') AS tstv  WHERE   ID % 2 = 0

I get output !Jospeh. What am i doing wrong

Upvotes: 2

Views: 200

Answers (2)

diiN__________
diiN__________

Reputation: 7656

Change SELECT endpos + 1 to SELECT endpos + LEN(@Delimiter).

Upvotes: 1

OlegAxenow
OlegAxenow

Reputation: 380

I hope this code suite your needs:

ALTER FUNCTION [dbo].[TfSplitTemplateVariable]
    (
      @String NVARCHAR(MAX),
      @Delimiter NVARCHAR(2)
    )
RETURNS @result TABLE (Id INT IDENTITY PRIMARY KEY, Data NVARCHAR (MAX))
AS
BEGIN
    DECLARE @start INT, @end INT
    SET @start = 1

    WHILE CHARINDEX(@Delimiter, @String, @start) > 0
    BEGIN
        SET @start  = CHARINDEX(@Delimiter, @String, @start) + LEN(@Delimiter)
        SET @end    = CHARINDEX(@Delimiter, @String, @start)

        IF @end > @start
        BEGIN
            INSERT @result(Data) VALUES(SUBSTRING(@String, @start, @end - @start))
        END
        ELSE
        BEGIN
            BREAK
        END

        SET @start = @end + LEN(@Delimiter)
    END

    RETURN
END
GO

Upvotes: 0

Related Questions