当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Converting couple of characters from nvarchar to int

问题描述:

declare @var1 nvarchar(100)

set @var1='C85,c84,c88'

set @var1=REPLACE(@var1,'C','')

select column24 from someTable where column23 in (@var1)

Hello guys and gurls, I am new around here and I need help with this. This query gives me nothing I know its beacouse var1 is nvarchar but i dont know how to make it work. Dont ask me why I used that REPLACE, I am testing something, in this particular query it looks stupid. So close your eyes on that one :)

And yes, there are rows 84,85,88 in someTable at column23. I am doing this only thru mssql management studio.

网友答案:

This should do the trick:

DECLARE @var1 NVARCHAR(100);
SET @var1 = N'C85,c84,c88';
SET @var1 = REPLACE(@var1,'C','');

--select @var1;
DECLARE @sql NVARCHAR(4000);
SET @SQL = N'select column24 from someTable where column23 in (' + @var1 + ')' ;
--SELECT @SQL; 
EXEC sp_executesql @SQL;
网友答案:

Try like this,

DECLARE @var1 NVARCHAR(100)

SET @var1 = 'C85,c84,c88'
SET @var1 = REPLACE(@var1, 'C', '')

SELECT column24
FROM someTable s
WHERE EXISTS (
        SELECT 1
        FROM (
            SELECT CAST('<XMLRoot><RowData>' + REPLACE(@var1, ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
            ) t
        CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
        WHERE s.column23 = LTRIM(RTRIM(m.n.value('.[1]', 'int')))
        )
分享给朋友:
您可能感兴趣的文章:
随机阅读: