获取“\"之间的子串其中多个“\" [英] Get substring between "\" where multiple "\"

问题描述

找到这个解决方案来获取斜杠 () 字符后的子串

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.

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

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

推荐答案

如果你真的想用 TSQL 来做，请看下文.

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

我已经玩过 SQL Fiddle 来展示它的工作，忽略 CROSS JOIN小提琴，他们只是绕过了 SQLFiddle 对 DECLARE 的限制.

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];

给出结果

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

<小时>

在我无法使用表值参数的 SQL 2005 数据库中，我使用 .Net CLR Split 来组合普通的 .Net Split 函数.使用合适的工具可以更简单、更快速地处理字符串.

---

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.

如果需要，这里有一个 NVarChar(MAX) 版本.

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];

这篇关于获取“\"之间的子串其中多个“\"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！