SQL知识：SQL高级字符处理函数

SQL是一种结构化查询语言，主要用于存取数据以及查询、更新和管理关系数据库系统。为了提高数据操作，掌握SQL高级字符处理函数很有必要。本文将为您介绍几种SQL高级字符处理函数，包括CHARINDEX、CONCAT_WS、FORMAT、PATINDEX、REPLICATE等的作用、语法、示例等，仅供大家参考。

1、CHARINDEX

作用

会在第二个字符表达式中搜索一个字符表达式，这将返回第一个表达式（如果发现存在）的开始位置。

语法

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

注：这是一个常用的字符搜索函数，起始下标是1，不是0。

如果加了起始下标，会从忽略起始下标前面的字符，往后面搜索。

不加起始值示例

SELECT CHARINDEX('数据',
'SQL数据库开发SQL数据库开发')

结果

添加起始值示例

SELECT CHARINDEX('数据',
'SQL数据库开发SQL数据库开发',6)

结果

2、CONCAT_WS

作用

以端到端的方式返回从串联或联接的两个或更多字符串值生成的字符串。它会用第一个函数参数中指定的分隔符分隔连接的字符串值。（CONCAT_WS 指示使用分隔符连接。）

语法

CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )

注：CONCAT_WS 会忽略列中的 NULL 值。用 ISNULL 函数包装可以为 null 的列，并提供默认值。

示例

SELECT CONCAT_WS('-','SQL',
'数据库',NULL,'开发')

结果：

3、FORMAT

作用

返回使用指定格式和可选区域性格式化的值。使用 FORMAT 函数将日期/时间和数字值格式化为识别区域设置的字符串。对于一般的数据类型转换，请使用 CAST 或 CONVERT。

语法

FORMAT ( value, format [, culture ] )

注意:

FORMAT 将返回 NULL 错误，而不是非 valid 的 culture 。例如，如果 format 中指定的值无效，则返回 NULL 。

FORMAT 函数具有不确定性。

FORMAT 依赖于 .NET Framework 公共语言运行时 (CLR) 的存在。

此函数无法进行远程处理，因为它依赖于 CLR 的存在。远程处理需要 CLR 的函数可能导致在远程服务器上出现错误。

日期格式示例

DECLARE @d DATETIME = '7/25/2024';
SELECT FORMAT(@d,'d','en-US') AS '美国格式'  
      ,FORMAT(@d,'d','en-gb') AS '英国格式'  
      ,FORMAT(@d,'d','de-de') AS '德国格式'  
      ,FORMAT(@d,'d','zh-cn') AS '中文格式';
  
SELECT FORMAT(@d,'D','en-US') AS '美国格式'  
      ,FORMAT(@d,'D','en-gb') AS '英国格式'  
      ,FORMAT(@d,'D','de-de') AS '德国格式'  
      ,FORMAT(@d,'D','zh-cn') AS '中文格式';

结果

自定义格式示例

SELECT FORMAT( GETDATE(),
    'dd/MM/yyyy', 'zh-cn' ) AS '自定义日期'  
      ,FORMAT(123456789,
'###-##-####') AS '自定义数字';

结果

数值格式示例

SELECT FORMAT(1.127456,
       'N','zh-cn') AS '四舍五入格式'  
       ,FORMAT(1.123456, 
       'G', 'zh-cn') AS '一般格式'  
       ,FORMAT(1.123456, 
       'C', 'zh-cn') AS '货币格式'

结果

4、PATINDEX

作用

返回模式在指定表达式中第一次出现的起始位置；如果在所有有效的文本和字符数据类型中都找不到该模式，则返回零。该函数与CHARINDEX比较相似

语法

PATINDEX ( '%pattern%' , expression )

注意

如果 pattern 或 expression 为 NULL，则 PATINDEX 返回 NULL 。

PATINDEX 的起始位置为 1。

PATINDEX 基于输入的排序规则执行比较。若要以指定排序规则进行比较，则可以使用 COLLATE 将显式排序规则应用于输入。

示例

SELECT PATINDEX('%数据库%', 'SQL数据库开发');

结果

在 PATINDEX 中使用通配符示例

使用 % 和 _ 通配符查找模式 '数'（后跟任意一个字符和 '库'）在指定字符串中的开始位置（索引从 1 开始）

SELECT PATINDEX('%数_库%', 'SQL数据库开发');

结果

5、REPLICATE

作用

以指定的次数重复字符串值。

语法

REPLICATE ( string_expression ,integer_expression )

示例

SELECT '2'+REPLICATE ('3',5)

结果

6、REVERSE

作用

返回字符串值的逆序。

语法

REVERSE ( string_expression )

示例

SELECT REVERSE('SQL数据库开发')

结果

7、STRING_AGG

作用

串联字符串表达式的值，并在其间放置分隔符值。不能在字符串末尾添加分隔符。

语法

STRING_AGG ( expression, separator ) [ <order_clause> ]

注意

STRING_AGG 是一个聚合函数，用于提取行中的所有表达式，并将这些表达式串联成一个字符串。表达式值隐式转换为字符串类型，然后串联在一起。隐式转换为字符串的过程遵循现有的数据类型转换规则。

如果输入表达式的类型为 VARCHAR，则分隔符的类型不能是 NVARCHAR。

null 值会被忽略，且不会添加相应的分隔符。若要为 null 值返回占位符，请使用 ISNULL 函数，如示例 B 中所示。

STRING_AGG 适用于任何兼容级别。

示例

WITH t AS (
 SELECT '张三' Name,'语文' Course,89 Score
 UNION ALL
 SELECT '张三' ,'数学' ,91 
  UNION ALL
 SELECT '李四' ,'语文' ,78 
  UNION ALL
 SELECT '李四' ,'数学' ,96 
 )

 SELECT Name,
 STRING_AGG(Course,',') Course ,
 STRING_AGG(Score,',') Score
 FROM t
 GROUP  BY Name

结果

8、STRING_SPLIT

作用

一个表值函数，它根据指定的分隔符将字符串拆分为子字符串行。

语法

STRING_SPLIT ( string , separator )

注意

STRING_SPLIT 输入包含分隔子字符串的字符串，并输入一个字符用作分隔符。STRING_SPLIT 输出其行包含子字符串的单列表。输出列的名称为“value”。

输出行可以按任意顺序排列。顺序不保证与输入字符串中的子字符串顺序匹配。可以通过在 SELECT 语句中使用 ORDER BY 子句覆盖最终排序顺序 (ORDER BY value)。

当输入字符串包含两个或多个连续出现的分隔符字符时，将出现长度为零的空子字符串。空子字符串的处理方式与普通子字符串相同。可以通过使用 WHERE 子句筛选出包含空的子字符串的任何行 (WHERE value <> '')。如果输入字符串为 NULL，则 STRING_SPLIT 表值函数返回一个空表。

示例一

SELECT Value FROM 
STRING_SPLIT('SQL-数据库-开发', '-');

结果

示例二

WITH t AS (
 SELECT 1 ID,'张三' Name,
 '足球,篮球,羽毛球' Hobby
 UNION ALL
 SELECT 2 ,'李四','足球,游泳,爬山'
 )


SELECT ID, Name, Value
FROM t
CROSS APPLY STRING_SPLIT(Hobby, ',');

结果

注意：返回的列不再是Hobby，而是Value，必须写成Value，否则得不到想要的结果。这个与STRING_AGG()函数的功能相反。

9、STUFF

作用

STUFF 函数将字符串插入到另一个字符串中。它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置。

语法

STUFF ( character_expression , start , length , replaceWith_expression )

注意

如果开始位置或长度值是负数，或者开始位置大于第一个字符串的长度，则返回 Null 字符串。如果开始位置为 0，则返回 Null 值。如果要删除的长度大于第一个字符串的长度，则删除到第一个字符串中的第一个字符。

如果结果值大于返回类型支持的最大值，则会引发错误。

示例

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

结果

重要声明：本文来自SQL数据库开发，经授权转载，版权归原作者所有，不代表锐成观点，转载的目的在于传递更多知识和信息。