TSQL 字符串修改 [英] TSQL String modify

问题描述

我的字符串有问题.我有一个变量@Rights，它看起来像ASD、ZXC、QWE、IOP、JKL"我需要做的是在

I have a problem with string. I have a variable @Rights which looks like 'ASD,ZXC,QWE,IOP,JKL' What I need to do is use this string in

SELECT * FROM dbo.Example
WHERE Rights IN (@Rights)

问题是我需要转换:

'ASD,ZXC,QWE,IOP,JKL'

到:

'ASD','ZXC','QWE','IOP','JKL'

我该怎么做?

推荐答案

可以创建拆分函数

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
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 after you can convert the string in a temp table

DECLARE @Rights NVARCHAR(128)
SET @Rights = 'ASD,ZXC,QWE,IOP,JKL'
SELECT *
INTO #Temp
FROM dbo.Split(@Rights , ',')

然后您可以像这样在查询中使用它

and after you can use it in your query like this

SELECT * FROM dbo.Example
WHERE Rights IN (SELECT Data FROM #Temp)

这篇关于TSQL 字符串修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！