SQL Server 中 INT 类型字段 IN 逗号字符串类型方法

[TOC]

## 1. SQL SERVER 中 INT 类型 IN 字符串

### 1.1. 背景

在一些项目中会将主键id拼接成一个id字符串（1,2,3,4,5），然后当成参数（@param）传到存储过程中，这时如果执行`IN(@param)`是会报错的，因为主键id是int类型，而参数是字符串，这时就需要将这个字符串拆分为多个独立的 int 值。下面提供了几种解决方案

### 1.2. 解决办法

#### 1.2.1. 使用字符串拆分函数（SQL Server 2016+）

SQL Server 2016 及以上版本提供了STRING_SPLIT函数，可以直接拆分字符串：

```SQL
SELECT *
FROM dbo.rentHouseTbl
WHERE id IN (
    SELECT CAST(value AS INT)
    FROM STRING_SPLIT('1,2,3', ',')
)
```

#### 1.2.2. 使用 XML 拆分（兼容低版本 SQL Server）

如果你的 SQL Server 版本较低（2016 之前），可以使用 XML 方法拆分字符串：

```SQL
SELECT *
FROM dbo.rentHouseTbl
WHERE id IN (
    SELECT CAST(RTRIM(LTRIM(m.n.value('.[1]','varchar(8000)'))) AS INT)
    FROM (
        SELECT CAST('<XMLRoot><RowData>' + REPLACE('1,2,3', ',', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
    ) AS t
    CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
)
```
#### 1.2.3. 创建拆分函数

对于旧版本的 SQL Server，可以创建一个自定义的字符串拆分函数：

```SQL
-- 创建拆分函数
CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Results TABLE (Value INT)
AS
BEGIN
    DECLARE @Position INT
    SET @Position = CHARINDEX(@Delimiter, @String)

WHILE @Position > 0
    BEGIN
        INSERT INTO @Results (Value)
        SELECT CAST(SUBSTRING(@String, 1, @Position - 1) AS INT)

SET @String = SUBSTRING(@String, @Position + 1, LEN(@String))
        SET @Position = CHARINDEX(@Delimiter, @String)
    END

IF @String <> ''
        INSERT INTO @Results (Value)
        SELECT CAST(@String AS INT)

RETURN
END
```

然后使用这个函数：

```SQL
SELECT * FROM dbo.rentHouseTbl
WHERE id IN (
    SELECT Value
    FROM dbo.SplitString('1,2,3', ',')
)
```

#### 1.2.4. 借助系统表spt_values分割

```SQL
DECLARE @str VARCHAR(2000) = '18,12,3,254,65';
WITH TEMP AS (
  SELECT A.number from master.dbo.spt_values A where type = 'p' and CHARINDEX(SUBSTRING(@str, number, 1), ',') > 0
  UNION ALL
  SELECT LEN(@str) + 1  -- 末尾没有分割符号，需加一位
)
SELECT SUBSTRING(@str, S.MI + 1, S.MA - S.MI - 1), S.MI, S.MA
FROM   (
  SELECT ISNULL((SELECT TOP (1) number FROM TEMP B WHERE B.number < A.number ORDER BY number DESC), 0) MI, A.number MA
  FROM   TEMP A
) S
WHERE  SUBSTRING(@str, S.MI + 1, S.MA - S.MI - 1) <> ''
```

**拆分后结果**

|分割后|MI|MA|
|--|--|--|
|18|0|3|
|12|3|6|
|3|6|8|
|254|8|12|
|65|12|15|