T-SQL——函数——字符串操作函数

目录

• 0. 加号（+）
• 1. LEFT和RIGHT
• 2. SUBSTRING
• 3. LEN和DATALENGTH
• 4. CHARINDEX和PATINDEX
• 5. REPLACE
• 6. REPLICATE和SAPCE
• 7. STUFF
• 8. REVERSE
• 9. UPPER和LOWER
• 10. LTRIM和RTRIM
• 11. QUOTENAME
• 12. 综合使用示例
  • 删除字符串最后一位
  • 计算某个字符串出现的次数
  • 字符串统一长度，不足则前面补零
  • 积累待补充中……

志铭-2021年10月10日 23:56:04

---

0. 加号（+）

SELECT 'abc'+'123'结果：abc123

注意若是varchar 类型+int 类型，需要将int类型转换为varchar类型，方能作为字符串相加

因为，当出现“XX+数字”或是“数字+XX”时，查询引擎会把“+”看做数学运算加法

SELECT 'abc' + CAST(18 AS VARCHAR(10)) 结果：abc18

1. LEFT和RIGHT

• 【定义】
  LEFT()返回从左往右指定长度的子字符串。
  RIGHT()返回从右往左指定长度的子字符串。

• 【语法】
  LEFT(expressionString,lengthInt)
RIGHT(expressionString,lengthInt)

  • 注意第二个参数是期望保留的长度

• 【示例】
  SELECT LEFT('abc',2)返回ab
SELECT RIGHT('abc',2)返回bc

2. SUBSTRING

• 【定义】 SUBSTRING()函数用于从字符串中提取子串

• 【语法】 SUBSTRING(expressionString,startInt,lengthInt)

  对字符串string，从start索引位置开始，返回length长度的子字符串

  注意这里的索引是从1开始数的

• 【示例】 SELECT SUBSTRING('abcdef',2,3)结果:bcd

• 【注意】 若是期望从某个起始位置开始获取直到结尾的所有字符，可以使用一个比目标字符串长度还大的值作为截取子串的长度参数，SUBSTRING将返回最大起始位置到目标字符串的结尾，而不会将多出的长度以空格填充。

  SELECT SUBSTRING('abc',1,9)结果是："abc",而不是"abc "

3. LEN和DATALENGTH

• 【定义】
  LEN函数返回指定字符串的字符数
  DATALENTH返回的是指定字符串的字节数

• 【语法】
  LEN(expressionString)
DATALENGTH(expressionString)

• 【示例】
  SELECT LEN('abc')结果：3
  SELECT LEN('张三')结果：2

  SELECT DATALENGTH('abc')结果：3
  SELECT DATALENGTH(N'abc')结果：6
  SELECT DATALENGTH('张三')结果：4

• 【注意】
  LEN函数是计算的是不包含尾随的空格，而DATALENGTH是会包含尾随的空格

  SELECT LEN('ab ')结果：2
  SELECT DATALENGTH('ab ')结果：3

4. CHARINDEX和PATINDEX

• 【定义】
  CHARINDEX用于寻找某一个字符在目标字符串第一次出现的起始位置（从1开始数）

  PATINDEX用于寻找某个模式的子字符串在目标字符串中第一次出现的起始位置

• 【语法】

  • CHARINDEX(str2Find,str2Search)
    • 注意第一个参数是待寻找的子串
  • PATINDEX('%pattern%',str2Search)

• 【示例】
  SELECT CHARINDEX('ab','abcab')结果：1

  • 这里'ab'在‘abcab'中出现两次，但是CHARINDEX函数返回的是第一次出现的位置

  SELECT PATINDEX('%[0-9]%','abc123abc')结果：4
  SELECT PATINDEX('a%','abc123')结果：1
  SELECT PATINDEX('a_c%','abc123')结果：1

  • 这里的第一个参数是和LIKE谓词后的模式规则相同

5. REPLACE

• 【定义】REPLACE函数可以将目标字符串中所有出现的某个子字符串替换为指定的字符串

• 【语法】
  REPLACE(expressionString,patternString,replacementString)

• 【示例】

  • 将字符串中的'ab'替换为'AB'
    • SELECT REPLACE('ab222ab333ab444','ab','AB')结果：AB222AB333AB444
  • 将字符串中的'ab'全部删除（即全部替换为空格）
    • SELECT REPLACE('ab222ab333ab444','ab','')结果：222333444

6. REPLICATE和SAPCE

• 【定义】
  REPLICATE用于将指定的字符串复制指定的次数

  SPACE用于返回指定长度的空格

• 【语法】
  REPLICATE(expressString,nInt)
SPACE(nInt)

• 【示例】
  SELECT REPLICATE('abc',3)结果：abcabcabc

  SELECT DATALENGTH(SPACE(10))结果：10

7. STUFF

• 【定义】
  将字符串中的一部分替换为另外一个子字符串。

  用于替换的子字符串不需要和被替换的部分长度一样。

• 【语法】
  STUFF(expressionString,startInt,lengthInt,expressionString2)

• 【示例】

  • 将目标字符串中从第4个字符之后的三个字符替换为指定的字符串
    SELECT STUFF('abc123def',4,3,'66666666')结果：abc66666666def

  • 将目标字符串中的第一个字符替换为空格（相当于删除目标字符串的第一个字符）
    SELECT STUFF(',a,b,c',1,1,'')结果：a,b,c

8. REVERSE

• 【定义】用于将指定的字符串中的字符颠倒过来

• 【语法】
  REVERSE(expressionString)

• 【示例】
  SELECT REVERSE('abc')结果：cba

9. UPPER和LOWER

• 【定义】将指定的字符串全部转换为大写或小写

• 【语法】
  UPPER(expressionString)
LOWER(expressionString)

• 【示例】

  SELECT UPPER('aBc')结果：ABC
  SELECT LOWER('aBc')结果：abc

10. LTRIM和RTRIM

• 【定义】用于将指定的字符串的前导空格和尾随空格删除

• 【语法】
  LTRIM(expressionString)
RTRIM(expressionString)

• 【示例】

  • 删除字符串前端的空格
    SELECT LTRIM(' abc')结果：abc
  • 删除字符串后端的空格
    SELECT RTRIM('abc ')结果：abc
  • 同时删除字符串前后的空格
    SELECT LTRIM(RTRIM(' abc '))结果：abc

11. QUOTENAME

• 【定义】默认用于给指定的字符串外加一个方括号
  这个函数的第二个参数是可选的，第二个参数只能是英文逗号（，）、英文引号（"）或默认的方括号（]）

• 【语法】
  QUOTENAME(expressionString,[character])

• 【示例】

  SELECT QUOTENAME('Name')结果：[Name]

  SELECT QUOTENAME('Name',')')结果：(Name)

  SELECT QUOTENAME('Name','"')结果："Name"

12. 综合使用示例

删除字符串最后一位

DECLARE @a VARCHAR(100)='a,b,c,'
--SELECT LEN(@a)--结果：6

--法1
SELECT LEFT(@a,LEN(@a)-1)
--结果：a,b,c

--法2
SELECT STUFF(@a,LEN(@a),1,'')
--结果：a,b,c

计算某个字符串出现的次数

• 实现思路：将需要计算特定子串出现的次数，全部替换为空格，之后使原始长度减去替换后的长度，即为特定子串出现的次数

DECLARE @str VARCHAR(100)='abcabc'
DECLARE @oldLength INT =LEN(@str)--@oldLength=6
DECLARE @newStr VARCHAR(100)=REPLACE(@str,'a','')--@newStr=bcbc
DECLARE @newLength INT=LEN(@newStr)--@newLength=4
SELECT	@oldLength-@newLength--结果：2

字符串统一长度，不足则前面补零

• 实现思路：甭管当前的长度，一律在该字符串前面加上9个0，之后在从右往左截取指定长度的子串作为结果值

DECLARE @tableVar TABLE
(
    No VARCHAR(100) NOT NULL
);
INSERT INTO @tableVar
(
    No
)
VALUES
('1'),
('12'),
('123'),
('1234'),
('1234');

--字符串统一长度为10，不足则前位补0
SELECT RIGHT(REPLICATE('0', 9) + No, 10)FROM @tableVar;--法1
SELECT REPLICATE('0', 10 - LEN(No)) + No AS NO FROM @tableVar;--法2

--结果
--0000000001
--0000000012
--0000000123
--0000001234
--0000001234


--字符串统一长度，不足则使用空格在后面补足
SELECT No + SPACE(10 - LEN(No)) FROM @tableVar;--法1
SELECT LEFT(NO+SPACE(10),10) FROM @tableVar;--法2

--验证上述操作：
SELECT DATALENGTH(No + SPACE(10 - LEN(No))) FROM @tableVar--验证法1
SELECT DATALENGTH( LEFT(NO+SPACE(10),10)) FROM @tableVar --验证法2

--结果：
--10
--10
--10
--10
--10

积累待补充中……