SQL SUBSTRING 函数

SQL Server 的 SUBSTRING 是用于从给定表达式返回指定数量字符的字符串函数之一。在实际的数据分析中，通常需要从字符串列中提取单词或部分内容。在这种情况下，您可以使用 Substring 函数从所需的字符串列中提取部分内容。

SQL Substring 函数可用于字符、图像、文本和二进制数据。它使用第三个参数来决定应返回多少个字符。

在本文中，我们将探讨 SQL Server SUBSTRING 函数的工作功能，从语法和基本变量示例开始。在后面的部分，我们将展示如何将其用于列，提取文本的开头和结尾，以及从电子邮件中查找域名。

SQL SUBSTRING 函数语法

它可用于字符、图像、文本和二进制数据，SUBSTRING 函数的语法如下所示。

SUBSTRING(String_Expression, Starting_Position, Length)

上述 SQL Server 字符串 substring 函数语法中的参数是：

• String_Expression：这是您想要从中返回或提取字符的内容。它可以是表达式、列名或字符串字面量。
• Starting_Position：此 INT 数据类型的参数定义起始位置。这是您需要确定服务器开始选择字符的索引位置。例如，1 = 从第一个位置开始，2 = 从第二个字符开始，依此类推。
• Length：这是一个可选参数。但是，使用此参数来指定要从表达式中提取多少个字符。

注意：如果您未提供长度参数值，SQL SUBSTRING 函数将返回从起始位置到末尾的所有字符。因此，为避免这种情况，您需要使用长度参数指定所需的字符数。

SQL Server Substring 函数示例

以下示例列表可帮助您理解 Substring 函数的语法以及利用此强大功能提取数据的众多方法。为此演示，我们将使用以下数据：

SQL Substring 来自字符串字面量

以下查询从给定的字符串变量中提取一部分并返回文本。在此示例中，SQL Server 代码的第一行声明了一个字符串变量，并为其分配了“Learn Any Program”文本。

DECLARE @Expression varchar(50)
SET @Expression = 'Learn Any Program' 

--Using Positive Integer
SELECT SUBSTRING(@Expression, 1, 5) AS 'SUB' 
SELECT SUBSTRING(@Expression, 3, 10) AS 'SUB' 

-- End Position is Greater than String Length
SELECT SUBSTRING(@Expression, 1, 20) AS 'SUB' 

-- Starting Position is Greater than Length
SELECT SUBSTRING(@Expression, 20, 25) AS 'SUB' 

在第一个 Select 语句中，我们将起始位置指定为 1，长度指定为 5。这意味着 SUBSTRING 函数将从位置 1 开始，并从该位置提取五个字符。

SELECT SUBSTRING(@Expression, 1, 5) AS 'SUB' 

下一个语句从索引位置 3 开始，返回 10 个字符。

SELECT SUBSTRING(@Expression, 3, 10) AS 'SUB'

接下来，我们将 substring 函数的第二个参数设置为 20。这里，原始表达式的长度为 17，而 20 大于字符串长度。因此，String Function 将返回 @Character_Expression 中的所有字符。

SELECT SUBSTRING(@Expression, 1, 20) AS 'SUB' 

在下一行中，我们将起始位置设置为 20，大于原始字符串长度。因此，它将返回一个空字符串，因为在该位置没有可返回的内容。

SELECT SUBSTRING(@Expression, 20, 25) AS 'SUB' 

Substring 来自表列

SQL Server Substring 函数还允许您从列值中提取或选择所需的字符数。在此示例中，我们使用此函数从 Department 名称字符串列的所有记录中提取从索引位置 2（起始位置）开始并返回九个字符的子字符串。我们还提到了另一行示例，该行从第 6 个位置开始并从中返回 17 个字符。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
	  ,SUBSTRING([DepartmentName], 2, 9) AS Dept
	  ,SUBSTRING([DepartmentName], 6, 17) AS DeptNew
      ,[Email]
  FROM [Employe]

Substring 在多个列上

有时，您可能需要从多个表列中提取和返回部分文本。SQL Server SUBSTRING 函数允许您将其应用于多个列。您还可以将此函数与 CONCAT 结合使用，以连接两个子字符串并创建新列。

在这里，我们将其同时应用于 Department 和 Email 列。

• Department 列：从第二个位置开始，返回 9 个字符（包括空格）
• Email 列：从索引位置 5 开始，并从 Email 列中返回五个字符（包括特殊字符）

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
	  ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
	  ,SUBSTRING(Email, 5, 5) AS [SQLSUBSTRING]
  FROM [Employe]

如果您观察到上面的结果，此字符串函数未能完美地获取域名，因为我们使用了固定值。在接下来的示例中，我们将展示如何使用动态值作为第二个和第三个参数来准确地返回域名。

SQL Server Substring 字符之前：CHARINDEX 示例

在某些情况下，您需要提取特殊字符或空格之前的文本。您可以结合使用 SUBSTRING、CHARINDEX 和 LEN 或 LEFT 来提取特定字符之前的文本。

最常见的情况是从电子邮件 ID 中提取 @ 符号之前的文本。在此示例中，我们将查找 Department Name 列中空格之前的文本。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
	  ,SUBSTRING([DepartmentName],
				CHARINDEX(' ', [DepartmentName]) + 1,
				LEN([DepartmentName])
				) AS Dept
      ,[Email]
FROM [Employe]

SQL Substring 字符之后查找电子邮件中的域名

在此示例中，我们查找 Email Address 列中 @ 符号之后的字符串。这意味着我们将使用 Substring、CHARINDEX 和 LEN 函数来查找电子邮件列中存在的域名。

在此示例中，我们使用 CHARINDEX 作为第二个参数来查找 @ 索引位置，并使用 LEN 函数来查找每行的列长度。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
	  ,SUBSTRING(Email, 
				CHARINDEX('@', Email) + 1,
				LEN(Email)
				) AS EmailString
  FROM [Employe]

下面的语句将返回 @ 符号的索引位置。这意味着 CHARINDEX Function 将检查每个记录中 @ 符号的索引位置。接下来，我们在索引位置加 1，因为域名从下一个位置开始。

CHARINDEX ('@', [Email]) + 1

下一行是使用 LEN Function 查找电子邮件长度。

LEN([Email])

总而言之，服务器将从 @ 符号之后的索引位置开始，并在字符串结束时停止。

SQL Substring 字符之前和之后

此示例是以上两个示例的组合。第一个语句返回 @ 符号之前的字符，第二个语句返回电子邮件 ID 的 @ 符号之后的字符。

在第一个语句中，起始位置为一，因此它必须从开头开始。接下来，CHARINDEX(‘@’, Email) 有助于识别 @ 符号的位置，SUBSTRING 函数返回直到 @ 的字符。如果您忘记从中减去一，则输出将包含 @ 符号。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
	  ,SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS EmailBefore
	  ,SUBSTRING(Email, CHARINDEX('@', Email) + 1,LEN(Email)) AS EmailAfter
FROM [Employe]

Substring 在 Where 子句中

我们也可以在 Where Clause 中使用此 SUBSTRING 函数，以基于少量字符而不是整个列来过滤 SELECT 语句的输出。下面的示例返回 Employee 表中 Department 名称以 r 结尾的所有记录。由于此函数不区分大小写，因此它会返回 Department 名称以 r 或 R 结尾的所有行。您也可以尝试 SUBSTRING([DepartmentName], -1) = ‘r’，结果将相同。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,[Email]
  FROM [Employe]
  WHERE SUBSTRING([DepartmentName], 
		LEN([DepartmentName]), 1) = 'r'

SQL Substring 在子查询中使用 LIKE 运算符

在这里，我们使用此方法从 SUBQUERY 中提取部分文本。子查询中的 Like Operator 将结果限制为少量记录。下面的查询将查找电子邮件 ID 以 Hotmail.com 结尾的记录。

SELECT SubQ.[FirstName]
      ,SubQ.[LastName]
      ,SubQ.[DepartmentName]
	  ,SUBSTRING(SubQ.[DepartmentName], 1, 8) AS SUB
	  ,SubQ.Email
FROM (
    SELECT * FROM [Employe]
    WHERE Email LIKE '%hotmail.com'
) AS SubQ;

Substring 在 Order 子句中

在此示例中，我们在 Order By Clause 中使用 SUBSTRING 函数，以使用此结果对数据进行排序。在下面的示例中，(Email, 4, 5) 将从索引位置 4 开始提取文本，并从中计数 5 个字符。ORDER BY 子句将使用提取的五个字符对记录进行排序。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
	  ,SUBSTRING([DepartmentName], 2, 9) AS Dept
      ,[Email]
	  ,SUBSTRING(Email, 4, 5) AS [Text]
  FROM [Employe]
  ORDER BY SUBSTRING(Email, 4, 5)

SQL Server Substring 负索引示例

它也允许您使用负索引，但这可能没有意义。例如，([DepartmentName], -2, 9) 从索引位置 -2 开始，并从中返回 9 个字符。由于我们没有从 -2 到 0 的任何记录（3 条记录），该函数将从 1 返回到 6（9 - 3）个字符。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], -2, 9) AS Dept
   ,SUBSTRING([DepartmentName], 2, 9) AS O_Dept
      ,[Email]
  FROM [Employe]
 FROM [Employe]

Susbtr 负索引示例 2

此负索引示例可帮助您了解如何编写最后 3 个或 4 个字符等。

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
   ,SUBSTRING([DepartmentName], 
 LEN([DepartmentName]) + 1 - 5, 4) AS Dept
      ,[Email]
   ,SUBSTRING([Email], LEN(Email) - 2, 3) AS EmailString
  FROM [Employe]

我们将取一条记录，即 Email 列的第一行，来解释输出。

([Email], LEN(Email) – 2, 3)

=> (ab@hotmail.com, 14 – 2, 3) => (ab@hotmail.com, 12, 3)

因此，函数将从索引位置 15 开始，并从中返回三个字符。（com）

SUBSTRING 解析文件路径或原始照片

此 SUBSTRING 函数可以从表列中的图像、文本、文件路径和二进制值中提取字符。

SELECT [PhotoID]
      ,[PhotoSource]
      ,[Photo]
	  ,SUBSTRING([PhotoSource], 1, 1) AS Drive
	  ,SUBSTRING([Photo], 1, 8) AS ShortPhoto
  FROM [SSIS Tutorials].[dbo].[PHOTOSOURCE]

SQL Server Substring PATINDEX 示例

通过结合使用 SUBSTRING 函数和 PATINDEX 函数，您可以使用正则表达式或模式提取文本部分。 PATINDEX function 在字符串中查找并返回给定正则表达式模式的起始位置。

SELECT [CustomerKey],[FirstName],[LastName],[EmailAddress],
	SUBSTRING([EmailAddress], 1, 
			PATINDEX('%[0-9]%', [EmailAddress] COLLATE Latin1_General_BIN) - 1)
             AS TextBefore,
    SUBSTRING([EmailAddress],
			PATINDEX('%[0-9]%', [EmailAddress] COLLATE Latin1_General_BIN), 
			LEN([EmailAddress])) AS TextAfter
FROM [DimCustomer]