activiti 功能概述
The requirement of data refactoring is very common and vital in data mining operations. In the previous article SQL string functions for Data Munging (Wrangling), you’ll learn the tips for getting started with SQL string functions, including the substring function for data munging with SQL Server. As we all agree that the data stored in one form sometimes require a transformation, we’ll take a look at some common functions or tasks for changing the case of a string, converting a value into a different type, trimming a value, and replacing a particular string in a field and so on.
数据重构的需求在数据挖掘操作中非常普遍且至关重要。 在上一篇文章Data Munging（Wrangling）SQL字符串函数中 ，您将学习SQL字符串函数的入门技巧，包括使用SQL Server进行数据字符串化的子字符串函数。 我们都同意，以一种形式存储的数据有时需要转换，因此，我们将介绍一些常见的函数或任务，这些函数或任务用于更改字符串的大小写，将值转换为其他类型，修整值并替换字段中的特定字符串，依此类推。
After reading this article, you’ll understand more about:
阅读本文后，您将了解更多有关以下内容的信息：
- SQL String functions SQL String函数
- Understand the SQL Server SUBSTRING function 了解SQL Server SUBSTRING函数
- How to handle data using it 如何使用它处理数据
- How to use the SQL Server SUBSTRING function in the where clause 如何在where子句中使用SQL Server SUBSTRING函数
- How to dynamically locate the starting and end character position 如何动态定位开始和结束字符位置
- How to work with date-time string using the SQL Server SUBSTRING function 如何使用SQL Server SUBSTRING函数处理日期时间字符串
- How to Create a simple sub-select using the T-SQL SUBSTRING function 如何使用T-SQL SUBSTRING函数创建简单的子选择
- And more… 和更多…
We’ll deep dive on above points in this article.. It allows us to truncate the length of string value that is varchar data-type, when we select data from the input string or tables.
我们将在本文的上述要点进行深入探讨。当我们从输入字符串或表中选择数据时，它可以截断varchar数据类型的字符串值的长度。
It takes three arguments.
它需要三个参数。
- field that we want to query on. 字段 。
- starting character, 起始字符 ，
- ending character 结尾字符
The SQL Server SUBSTRING function syntax is as follows:
SQL Server SUBSTRING函数的语法如下：
SUBSTRING (expression, position, length)
SUBSTRING（表达式，位置，长度）
Parameters:
参数：
- expression: Input source string expression ：输入源字符串
- position: Is an integer value that specifies the initial position from which the characters can be extracted from the given expression. The first position of an expression is always starting with 1. The initial position can be a negative integer. position ：是一个整数值，它指定可以从给定表达式中提取字符的初始位置。 表达式的第一个位置始终以1开头。初始位置可以为负整数。
- length: Is a positive integer value. It specifies the ending limit and determines how many characters are going to be extracted from the given expression. length ：是一个正整数。 它指定结束限制，并确定将从给定表达式中提取多少个字符。
Note: The SQL Substring function traversal is always from left to right.
注意：SQL Substring函数遍历始终是从左到右。
例子 (Examples)
In this section, we are going to deal with some real-world scenarios using SQL string functions. For few demos, the Adventureworks2016 database is used and for some other, SQL data is generated manually. Let’s get our hands dirty and see more action.
在本节中，我们将使用SQL字符串函数处理一些实际情况。 对于少数演示，使用Adventureworks2016数据库，而对于其他一些演示，则手动生成SQL数据。 让我们动起来，看看更多的动作。
- Simple data handling with the SQL Server Substring function 使用SQL Server Substring函数进行简单的数据处理
Let’s start with a basic SQL query. The following example returns a portion of a character string starting at an initial position 1 and extracts 5 characters from the starting position. The T-SQL SUBSTRING function is very useful when you want to make sure that the string values returned from a query will be restricted to a certain length.
让我们从一个基本SQL查询开始。 以下示例返回从初始位置1开始的字符串的一部分，并从开始位置提取5个字符。 当您要确保从查询返回的字符串值将被限制为特定长度时，T-SQL SUBSTRING函数非常有用。
SELECT FirstName, substring(firstname,1,5), lastname FROM Person.Person
In the following output, by using the SQL Server SUBSTRING function and specifying the ‘firstname’ column, the starting position of one, and the length of five characters, executing this SQL query will truncate the length of the strings that are returned from the table to five characters long. It doesn’t matter if the value itself in the table is longer than five characters.
在以下输出中，通过使用SQL Server SUBSTRING函数并指定“ firstname”列，一个的起始位置和五个字符的长度，执行此SQL查询将截断从表返回的字符串的长度。到五个字符长。 表格中的值本身是否超过五个字符都没有关系。
In the following, the 3rd parameter, the length, defined as 15. This will ensure that it doesn’t matter the length of the data stored in the table column itself, the query will only return the first 15 characters. This can be helpful to make sure that the output of query data is formatted according to our expectations or what our application requires.
在下面的第三个参数中，长度定义为15。这将确保表列本身中存储的数据的长度无关紧要，查询将仅返回前15个字符。 这有助于确保根据我们的期望或应用程序的要求对查询数据的输出进行格式化。
SELECT FirstName, substring(firstname,1,15), lastname FROM Person.Person
In the following, change the starting position as well as the length parameter, the length, defined as 10.
接下来，更改起始位置以及length参数，即length，定义为10。
SELECT FirstName, substring(FirstName,2,5), lastname FROM Person.Person
Extending the length to 10 characters will show longer string values. And changing the starting position to 2 will start counting characters from 2 through the string. In this case, substring function extracts 10 characters of the string starting at the second position. The SUBSTRING SQL function is very useful when you want to make sure that the string values returned from a query will be restricted to a certain length.
将长度扩展到10个字符将显示更长的字符串值。 并将起始位置更改为2将开始从2到字符串开始计算字符。 在这种情况下，子字符串函数从第二个位置开始提取字符串的10个字符。 当您要确保从查询返回的字符串值将被限制为一定长度时，SUBSTRING SQL函数非常有用。
So you’re getting an idea of how the SQL SUBSTRING function works. The field that we want to act on, we start at which character, and we end at which character
这样您就可以了解SQL SUBSTRING函数的工作方式。 我们要作用的领域，我们从哪个角色开始，然后从哪个角色结束
- Use of the SQL Server SUBSTRING function in the where clause 在where子句中使用SQL Server SUBSTRING函数
In the following example, using the ‘firstname’ column, the last two characters are matched with the word ‘on’ using the SQL SUBSTRING function in the where clause.
在下面的示例中，使用“名字”列，使用where子句中SQL SUBSTRING函数将最后两个字符与单词“ on”匹配。
SELECT DISTINCT FirstName, lastname
FROM
Person.Person
WHERE
SUBSTRING(FirstName, LEN(FirstName)-1,2) = 'on'
- Dynamically locate the starting and end character 动态定位开始和结束字符
In the following example, the input string has alpha-numeric characters. Using the SQL Server Substring function, the numeric sub-set of a string from the column col is transformed using CHARINDEX. You can also use the SQL string function PATINDEX to find the initial position and length parameter of the SQL SUBSTRING function.
在以下示例中，输入字符串包含字母数字字符。 使用SQL Server Substring函数，使用CHARINDEX转换col列中字符串的数字子集。 您还可以使用SQL字符串函数PATINDEX查找SQL SUBSTRING函数的初始位置和长度参数。
DROP TABLE IF EXISTS Dummy;
CREATE TABLE Dummy (col varchar(20));
INSERT INTO Dummy
(col)
VALUES
('NY-123 US'),
('AZ-456 GB'),
('MI-789 MO');
select substring
(col,
charindex('-',col,1)+1,
charindex(' ',col,1)-charindex('-',col,1)
) from Dummy;
OR
要么
DROP TABLE IF EXISTS Dummy;
CREATE TABLE Dummy (col varchar(20));
INSERT INTO Dummy
(col)
VALUES
('NY-123 US'),
('AZ-456 GB'),
('MI-789 MO');
select substring
(col,
PATINDEX('%-%',col)+1,
PATINDEX('% %',col)- PATINDEX (%-%,col)
) from Dummy;
In this example, using the SQL PATINDEX function, the initial position the string ‘-‘ is found. But the numeric value only starts in the next position so ‘1’ is added to initial position. Similarly, length is calculated by searching the next position ‘ ‘(space) and subtracting its value with the initial position gives the length. Now, we have values for all the arguments. Run the T-SQL statement.
在此示例中，使用SQL PATINDEX函数，找到字符串'-'的初始位置。 但是数值仅从下一个位置开始，因此将“ 1”添加到初始位置。 同样，通过搜索下一个位置''（空格）并用初始位置减去其值即可得出长度。 现在，我们有了所有参数的值。 运行T-SQL语句。
- Working with DateTime strings 使用DateTime字符串
In the following example, you can see that the column col has a data-set and it is a datetime string. Using the SQL Server SUBSTRING function, the input values are truncated using CHARINDEX or PATINDEX function to get the date-time value. And then the derived string is type-casted to DateTime so that it can be used to compare with other DateTime values. In this case, it’s compared against the SQL GETDATE() function.
在下面的示例中，您可以看到col列具有数据集，并且它是日期时间字符串。 使用SQL Server SUBSTRING函数，使用CHARINDEX或PATINDEX函数将输入值截断以获取日期时间值。 然后将派生的字符串类型转换为DateTime，以便可以将其与其他DateTime值进行比较。 在这种情况下，将其与SQL GETDATE（）函数进行比较。
You can easily find the initial position and convert the data to required data-type (valid values) using the convert or cast functions. Using CHARINDEX, search for the position of ‘/’ of the input column. After finding the position, the value is subtracted by ‘3’ to get an initial value ‘12’ for the SQL SUBSTRING function. Similarly, the search is made to find a position for the character ’,’(comma). In this way, subtracting value with the initial position will yield the length of the string.
您可以轻松地找到初始位置，并使用转换或转换功能将数据转换为所需的数据类型（ 有效值 ）。 使用CHARINDEX，搜索输入列的“ /”位置。 找到位置后，将值减去“ 3”以获取SQL SUBSTRING函数的初始值“ 12”。 类似地，进行搜索以找到字符“，”（逗号）的位置。 这样，用初始位置减去值将得出字符串的长度。
DROP TABLE IF EXISTS Dummy;
CREATE TABLE Dummy (col varchar(100));
INSERT INTO Dummy
VALUES
('The Date is 04/18/2015 08:00:00, a Saturday'),
('The Date is 02/20/2016 07:00:00, a Sunday'),
('The Date is 03/13/2017 10:00:00, a Monday'),
('The Date is 06/07/2018 09:00:00, a Tuesday')
GO
select col,charindex('/',col,1)-3,charindex(',',col,1)-charindex('/',col,1) ,
substring
(col,
charindex('/',col)-3,charindex(',',col)-charindex('/',col)+3
) from Dummy
select col,patindex('%is%',col)+4,patindex('%,%',col),patindex('%,%',col)-patindex('%is%',col)-5,
substring
(col,
patindex('%is%',col)+4,patindex('%,%',col)-patindex('%is%',col)-4
)
from Dummy
where getdate()-300>=cast(substring
(col,
patindex('%is%',col)+4,patindex('%,%',col)-patindex('%is%',col)-4) as datetime)
- Creating a simple sub-select 创建一个简单的子选择
A Sub-select, in SQL Server, is effectively a nested select statement. In SQL, the result of a select statement is effectively a table. It usually just exists in memory but it can always be used, as you would use a table. Because of this, a select statement may be used as a data source for another select statement
在SQL Server中，子选择实际上是嵌套的选择语句。 在SQL中，选择语句的结果实际上是一个表。 它通常仅存在于内存中，但是可以像使用表一样始终使用。 因此，一个选择语句可以用作另一个选择语句的数据源。
In the following example, you can see how the columns are transformed using the SQL Server SUBSTRING function and used as a table for the SQL join statement.
在下面的示例中，您可以看到如何使用SQL Server SUBSTRING函数转换列并将其用作SQL join语句的表。
If you see the temp table values, the first two characters of the first column represent state and next four characters represents the state-code. Similarly, the second column, the first two characters represents the country and rest four characters form the country-code. Using the SQL SUBSTRING function, the two columns are effectively parsed and transformed as four new columns. These columns can be used just as if they were a table in a database. In the select statement, it’s joined with the country table so that, we can actually find the name from the country.
如果您看到临时表的值，则第一列的前两个字符代表状态，接下来的四个字符代表状态码。 同样，在第二列中，前两个字符代表国家/地区，其余四个字符组成国家/地区代码。 使用SQL SUBSTRING函数，这两列被有效地解析并转换为四个新列。 这些列的使用就像它们是数据库中的表一样。 在select语句中，它与country表结合在一起，以便我们实际上可以从国家/地区中找到名称。
DROP TABLE IF EXISTS Country;
CREATE TABLE Country ( CCode int, CNAME VARCHAR(20))
INSERT INTO Country VALUES ( 1234,'Great Britain'),(5678, 'UNITED STATES'),
(4567, 'FRANCE' )
SELECT * FROM Country
DROP TABLE IF EXISTS temp;
CREATE TABLE temp ( Id1 VARCHAR(6), Id2 VARCHAR(6) )
INSERT INTO temp VALUES ( 'NY1234', 'US5678' ),
( 'AZ5678', 'GB1234' ),
( 'CA9012', 'FR4567' )
SELECT * FROM temp;
SELECT SUBSTRING(Id1, 1, 2) AS State, SUBSTRING(Id1, 3,len(ID1)) AS SCode, SUBSTRING(Id2, 1, 2) AS Country,
SUBSTRING(Id2, 3,len(id2)) AS CCode FROM temp;
SELECT co.CName, ss.CCode FROM
Country co
INNER JOIN (
SELECT SUBSTRING(Id1, 1, 2) AS State, SUBSTRING(Id1, 3,len(ID1)) AS SCode, SUBSTRING(Id2, 1, 2) AS Country,
SUBSTRING (Id2, 3, len(id2)) AS CCode FROM temp
) AS ss
ON co.CCode = ss.CCode
;
Note: You can also use RIGHT and LEFT string functions. You can refer to the SQL string functions for Data Munging (Wrangling) article for more information.
注意：您也可以使用RIGHT和LEFT字符串函数。 有关更多信息，您可以参阅“ 用于数据整理（争用）的SQL字符串函数”一文。
SELECT left(Id1,2) AS State, right(Id1, 4) AS SCode, left(Id2,2) AS Country,right(Id2, 4) AS CCode FROM temp;
摘要 (Summary)
So far, we’ve seen several examples of the SUBSTRING function in SQL Server, the character functions that SQL server makes readily available for use, and how you can use them to manipulate string values in your database and in your result set. In this way it is helpful to make sure that the output of SQL query data is formatted according to the expectations or business requirement.
到目前为止，我们已经看到了SQL Server中SUBSTRING函数的几个示例，SQL Server使其易于使用的字符函数，以及如何使用它们来操纵数据库和结果集中的字符串值。 这样，确保根据期望或业务需求格式化SQL查询数据的输出将很有帮助。
We also need to understand the importance of the data-set. It is always recommended to thoroughly validate the input value. There are multiple ways to transform the data using the T-SQL SUBSTRING function. In a few cases, it is possible to transform using other SQL string functions. In some cases, volume of data, performance, and SQL Server version defines the options one over the other.
我们还需要了解数据集的重要性。 始终建议彻底验证输入值。 使用T-SQL SUBSTRING函数有多种方式转换数据。 在某些情况下，可以使用其他SQL字符串函数进行转换。 在某些情况下，数据量，性能和SQL Server版本定义了一个选项。
That’s all for now. I hope you enjoyed this article on SQL string functions and the SQL Server SUBSTRING function in particular. Feel free to ask any questions in the comments below.
目前为止就这样了。 我希望您喜欢这篇有关SQL字符串函数，尤其是SQL Server SUBSTRING函数的文章。 随时在下面的评论中提问。
翻译自: https://www.sqlshack.com/sql-substring-function-overview/
activiti 功能概述
扫一扫
8579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
