主页 / dba / 问题 / 22175
Accepted
joe
Asked: 2012-08-09 07:06:17 +0800 CST

CHARINDEX OR IN 等价于整数

  • 772

我想要与此等效的整数。

--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0

所以像这样。

--Invalid SQL
DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
select * FROM Person AS p
WHERE p.ixPerson IN(@users
sql-server t-sql

6 个回答

  1. Best Answer

    您还没有真正指出如何调用它。您是否真的有一个充满逗号分隔的整数的字符串，或者这只是系统其他方面的产物？

    如果围绕此要求的外部环境是可变的，那么通常在 SQL Server 2008 及更高版本中，我传递一个包含 ID 列表（可能来自 ADO.NET 或其他）的表值参数，然后将其用作过程中的表 - 即WHERE value IN (SELECT id FROM @table)或INNER JOIN @table AS t ON t.id = users.t

    • 4

  2. 选项 1（第一部分直接来自Adam Machanic 的博客）

    DECLARE @number_of_numbers INT = 100000;
;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
numbers AS
(
SELECT TOP(@number_of_numbers)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM f
)
SELECT [your],[required],[columns],[here]
FROM Person where Person.ixPerson in (select number from numbers);

    当然，这只是创建数字表的一种方法。如果您正在寻找随机分类的 id，您可以使用表格变量或其他东西快速填充数字表格。但是，您必须将代码中的列表或使用 billinkc 建议的 UDF 分开，此时构建该IN子句可能会更好。

    选项 2（在此处使用@CadeRoux 的答案）：

    CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  );

declare @nums nvarchar(100);
set @nums = N'1,2,5,10,87';
SELECT [your],[required],[columns],[here]
    FROM Person where Person.ixPerson in (select CAST(s AS INTEGER) s from dbo.Split(N',',@nums));
    • 3

  3. 我会看一个数字表类型的解决方案，将分隔的数据拆分成可以使用的东西https://stackoverflow.com/questions/8956671/how-to-convert-varchar-to-integer-in-sql-server -stored-procedure/8958460#8958460然后你可以使用IN子句就好了

    • 1

  4. 我希望这个简单的解决方案在许多情况下都可以正常工作，尽管它并不能特别扩展（但您的问题中的示例也没有使用 CHARINDEX）：

    DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
SELECT *
FROM Person AS p
WHERE ',' + @users + ',' LIKE '%,' + CAST(VARCHAR(10), p.ixPerson) + ',%'
    • 1

  5. 我不喜欢这个解决方案，但这就是我所做的。

    DECLARE @users AS nvarchar(MAX)
SET @users = ('8,10,28')

DECLARE @finalCommand AS NVARCHAR(MAX)
SET @finalCommand = 'SELECT p.sFullName, *
                 FROM   Person as p
                 WHERE  p.ixPerson IN (' +  @users + ')'

EXEC sp_executesql  @finalCommand
    • 0 
  6. DECLARE @IDs AS nvarchar(50);
SET @IDs = N'1,2,8,23';

SELECT p.*
FROM dbo.Person AS p
WHERE CHARINDEX(N',' + CONVERT(nvarchar(11), p.ID) + N',', N',' + @IDs + N',') > 0;
    • 0

相关问题