SQL Server中字串包含字符串判断的函数CHARINDEX与PATINDEX、查询字符串指定字符出现的次数

SQL Server 中字符串函数使用

[TOC]

## 1. 获取字符串位置

获取指定字符或字符串出现在目标字符串中的位置

### 1.1. charindex

**微软官方文档：**中文：[《CHARINDEX (Transact-SQL)》](https://learn.microsoft.com/zh-cn/sql/t-sql/functions/charindex-transact-sql?view=sql-server-ver16)

#### 1.1.1. 介绍

**语法**：charindex(expression1,expression2,[ , start_location])

**作用：**从 expression2 字符串中指定的位置处开始查找是否包含 expression1 字符串

**返回值：**如果能从 expression2 字符串中查找到 expression1 字符串则返回 expression1 在expression2 出现的位置；反之返回0

#### 1.1.2. 示例

```SQL
select charindex('cd','abcdefg')
--结果为：3
select charindex('dc','abcdefg')
--结果为：0
select charindex('bCD','abcdefg')
--结果为：2
--charindex参数默认是不区分大小写
select charindex('bCD','abcdefg'COLLATE Latin1_General_CS_AS)
--结果为：0，如果在参数后面加了COLLATE Latin1_General_CS_AS说明是区分到小写的，对应的COLLATE Latin1_General_CI_AS是不区分大小写也就是默认的
```

#### 1.1.3. 用途说明

如果使用 `charindex` 来作为检索条件来替代 `like` 的话，性能上有优略势，如果使用 `like '%key%'` 的话，在数据量比较大的情况下使用 `charindex` 性能会有所提升，如果是使用 `like '%key'` 或则 `like 'key%'` 在数据量大的情况下要比 `charindex` 高，所以 `charindex` 不一定会比 `like` 性能高，要看使用的场景，检索的模糊方式

#### 1.1.4. 注意事项

+ 字符位置从 1 开始（而非 0），这是 SQL Server 字符串函数的通用规则。

+ 区分大小写：默认情况下，CHARINDEX 的匹配规则依赖数据库的排序规则（Collation）。若需强制区分 / 不区分，可指定排序规则：
```SQL
-- 强制区分大小写
SELECT CHARINDEX('Test', 'abc_test_123' COLLATE Latin1_General_CS_AS) AS Position; -- 输出 0
-- 强制不区分大小写
SELECT CHARINDEX('Test', 'abc_test_123' COLLATE Latin1_General_CI_AS) AS Position; -- 输出 5
```

### 1.2. patindex

#### 1.2.1. 介绍

和 charindex 一样 patindex 也可以用来判断一个字符串是否包含另一个字符串，两种的差异在于，charindex 是全匹配，patindex 支持模糊匹配

#### 1.2.2. 示例

```SQL
select patindex('%ter%','interesting data')
--结果为：3
select patindex('%t_ng%','interesting data')
--结果为：8
patindex也支持大小写敏感，做法和charindex一样
```

## 2. 获取字符串信息

### 2.1. len

#### 2.1.1. 示例

SQL Server 查询字符串指定字符出现的次数

这里提取指定符串"A"在keyword字段中的出现次数SQL为：

```
select len(keyword)-len(replace(keyword, 'A', ' ')) from 表
```

> 原理：用replace函数将要查找的字符替换为空字符，将替换之前的字符串长度-替换后字符串长度

## 3. 截取字符串

### 3.1. LEFT

LEFT，从左开始向后截取指定位数字符

只有两个参数，第一个参数是需要去除首尾的字符串，第二个参数是要从字符串中获取多少位字符

#### 3.1.1. 示例

```SQL
DECLARE @str VARCHAR(max)
SET @str='小明,小红,小刚,'
select LEFT(@str,LEN(@str)-1)
-- 结果：小明,小红,小刚
```

### 3.2. RIGHT

RIGHT，从右开始向前截取指定位数字符

只有两个参数，第一个参数是需要去除首尾的字符串，第二个参数是要从字符串中获取多少位字符

#### 3.2.1. 示例

```SQL
DECLARE @str2 VARCHAR(max)
SET @str2=',小明,小红,小刚'
select RIGHT(@str2,LEN(@str)-1)
结果：小明,小红,小刚
```

### 3.3 SUBSTRING

**微软官方文档：** 中文：[《SUBSTRING (Transact-SQL)》](https://learn.microsoft.com/zh-cn/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver16)、英文：[《SUBSTRING (Transact-SQL)》](https://learn.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver16)

**作用：**有范围的提取字符串中的内容，它是返回 SQL Server 中的字符、二进制、文本或图像表达式的一部分

#### 3.3.1. 语法介绍

**语法：**SUBSTRING(expression, start, length)

**返回：**如果表达式是支持的字符数据类型之一，则返回字符数据。如果 Expression参数 是支持的二进制数据类型之一，则返回二进制数据，返回的字符串类型与指定表达式的类型相同（表中显示的除外）

> **适用于：**SQL Server、Azure SQL 数据库、Azure SQL 托管实例、Azure Synapse Analytics、Analytics Platform System (PDW)、Microsoft Fabric 中的 SQL 分析终结点、Microsoft Fabric 中的仓库

#### 3.3.2. 示例

```SQL
SELECT name,
 SUBSTRING(name, 1, 1) AS NormalOne,
 SUBSTRING(name, 3, 2) AS NormalTwo,
 SUBSTRING(name, 2, 16) AS Beyond,
 SUBSTRING(name, 0, 3) AS Star0,
 SUBSTRING(name, -1, 2) AS StarNegativeOne,
 SUBSTRING(name, -1, 3) AS StarNegativeTwo
FROM sys.databases
WHERE database_id < 5;
```

**结果：**

|name|NormalOne|NormalTwo|Beyond|Star0|StarNegativeOne|StarNegativeTwo|
|--|--|--|--|--|--|--|
|master|m|st|aster|ma||m|
|tempdb|t|mp|empdb|te||t|
|model|m|de|odel|mo||m|
|msdb|m|db|sdb|ms||m|

#### 3.3.3. 非常规类型示例

本文本节指的非常规类型指的是binary/varbinary/image类型，这些类型存在在数据库中是以字符的形式存储的（例如图片存储的是0x……格式的16进制字符），Substring函数处理的是字符串，因此使用Substring函数提取字符时需要注意start和length参数都要乘于2，`start*2`、`length*2`

```SQL
USE pubs;
SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo,
   SUBSTRING(pr_info, 1, 10) AS pr_info
FROM pub_info
WHERE pub_id = '1756';
```

**结果：**

|pub_id |logo |   pr_info|
|--|--|--|
|1756  | 0x474946383961E3002500| This is sa|

> 备注：上面示例代码是从官方摘取的，使用该示例需要安装 pubs 数据库

## 4. 修改字符串

### 4.1. STUFF

#### 4.1.1. 介绍

**语法：**STUFF ( character_expression , start , length , replaceWith_expression )

#### 4.1.2. 示例

```SQL
DECLARE @str VARCHAR(max)
SET @str=',小明,小红,小刚,'
SELECT STUFF(@str,1,1,'名称：') addStr,STUFF(@str,9,2,'') str1,STUFF(@str,10,1,'') str2,STUFF(@str,10,1,'?') symbolStr
```

**结果：**

|addStr|str1|str2|symbolStr|
|--|--|--|--|
|名称：小明,小红,小刚,|,小明,小红,小|,小明,小红,小刚|,小明,小红,小刚?|

> 从结果中我们可以看出 `replaceWith_expression` 可以替换掉我们我们指定位置的字符串，第一句我们用【名称：】空字符串替换掉了最前面的【,】号，第二句就是我们用【''】替换掉从第9位开始的之后2个字符【刚,】，第三句则是用【''】替换掉了最后以为的【,】，最后一句就是用【?】替换掉最后一位的【,】

## 参考资料

> 微软官方：
[《Transact-SQL 语法约定 (Transact-SQL》)](https://learn.microsoft.com/zh-cn/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql?view=sql-server-ver16)