Get substring between "\" where multiple "\"

Question

Found this solution to get substring after slash () character

DECLARE @st1 varchar(10)
SET @st1 = 'MYTEST\aftercompare'
SELECT @st1
,SUBSTRING(@st1, CHARINDEX('\', @st1) + 1, LEN(@st1))

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c3a5e2c-54fc-43dd-b12c-1a1f6784d7d8/tsql-get-substring-after-slash-character

But is there a way to get substring after second slash or even more?

DECLARE @st1 varchar(50)
--Added more slashes
SET @st1 = 'MYTEST\aftercompare\slash2\slash3\slash4'
SELECT @st1
--This part would need some work
--,SUBSTRING(@st1, CHARINDEX('\', @st1) + 1, LEN(@st1))

And getting only the substring between the slashes.

Values: [1] "aftercompare" - [2] "slash2" - [3] "slash3" - [4] "slash4"

Why do you want to do this in T-SQL? Your client language will be much, much, much more efficient at this. — Aaron Bertrand
– Aaron Bertrand, Commented Nov 6, 2013 at 17:26
Working with a table where the column information is like that format: "8:00-10:00 \ Male \ 4 Division \ Room1" "10:00-12:00 \ Female \ 8 Division \ Room2" And do not have access to the original insert. And want to create dimensions for each scenario. — AceAlfred
– AceAlfred, Commented Nov 6, 2013 at 17:37

Jodrell · Accepted Answer · 2013-11-07 10:39:57Z

If you really want to do it in TSQL, see below.

I've gamed SQL Fiddle into showing it working, ignore the CROSS JOIN's in the fiddle, they just get around SQLFiddle's limitation over DECLARE.

DECLARE @s varchar(8000);
DECLARE @sep char;

SET @s = 'MYTEST\aftercompare\slash2\slash3\slash4';
SET @sep = '\';

WITH [splits] AS (
    SELECT
        0 [index],
        CHARINDEX(@sep, @s) [pos],
        0 [lastPos]
    UNION ALL
    SELECT
        [index] + 1,
        CHARINDEX(@sep, @s, [pos] + 1),
        [pos]
    FROM [splits]
    WHERE
        [pos] > 0)
SELECT
    [index],
    SUBSTRING(
        @s,
        [lastPos] + 1,
        CASE WHEN [pos] = 0
            THEN 8000
            ELSE [pos] - [lastPos] - 1
        END) [value]
FROM [splits];

gives the result

INDEX   VALUE 
0       MYTEST 
1       aftercompare 
2       slash2 
3       slash3 
4       slash4

In a SQL 2005 database where I couldn't use table value parameters I made .Net CLR Split to compose the normal .Net Split function. String manipulation is simpler and faster with the right tools.

If required, here is a NVarChar(MAX) version.

DECLARE @s nvarchar(max);
DECLARE @sep nchar;

SET @s = N'MYTEST\aftercompare\slash2\slash3\slash4';
SET @sep = N'\';

WITH [splits] AS (
    SELECT
        CAST(0 AS bigint) [index],
        CHARINDEX(@sep, @s) [pos],
        CAST(0 AS bigint) [lastPos]
    UNION ALL
    SELECT
        [index] + 1,
        CHARINDEX(@sep, @s, [pos] + 1),
        [pos]
    FROM [splits]
    WHERE
        [pos] > 0)
SELECT
    [index],
    SUBSTRING(
        @s,
        [lastPos] + 1,
        CASE WHEN [pos] = 0
            THEN 2147483647
            ELSE [pos] - [lastPos] - 1
        END) value
FROM [splits];

Nice solution, now I just need to update it towards my tables. Thank you!

DebugDevNull · Accepted Answer · 2013-11-06 18:00:38Z

You could use this table valued function to return a table with individual rows for each splitted result:

    ALTER FUNCTION [dbo].[Split]
    (
        @sString nvarchar(2000),
        @cDelimiter nchar(1)
    )
    RETURNS 
    @TblSplits TABLE 
    (
        SplitText nvarchar(2000)
    )
    AS
    BEGIN
        if @sString is null return
        declare @iStart int,
                @iPos int
        if substring( @sString, 1, 1 ) = @cDelimiter 
        begin
            set @iStart = 2
            insert into @TblSplits
            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 @TblSplits
                values  ( substring( @sString, @iStart, @iPos-@iStart ))
            else
                insert into @TblSplits
                values( null )
            set @iStart = @iPos+1
            if @iStart > len( @sString ) 
                break
        end

        DELETE @TblSplits WHERE SplitText IS NULL;

        RETURN
    END

with this you can call the function like this:

        DECLARE @st1 nvarchar(50)
        --Added more slashes
        SET @st1 = 'MYTEST\aftercompare\slash2\slash3\slash4'
        SELECT @st1

        SELECT * from dbo.Split(@st1,N'\');

Collectives™ on Stack Overflow

Get substring between "\" where multiple "\"

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related