How to intercept part of a field using sql statement

In SQL, you can use the SUBSTRING statement to intercept part of a field. The syntax format is "SUBSTRING (string, starting position, interception length)". The substring function in SQL is used to grab part of a field data

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer .

SUBSTRING ( expression, start, length )

Parameters

expression

String, binary string, text, image, column, or expression containing columns. Do not use expressions containing aggregate functions.

start

Integer or expression that can be implicitly converted to int, specifies the starting position of the substring, and the index starts from 1.

length

An integer or an expression that can be implicitly converted to int, specifying the length of the substring. After testing, it was found that can only be a non-negative number .

Return value

1. If expression is a supported binary data type, binary data is returned. We will not discuss this case for now.

2. If expression is a supported character data type, character data is returned.

(1) If the index of start starts from 1, the string will be intercepted from the first character of the expression, and from 2, it will be intercepted from the second character of the expression. analogy.

For example:

 select SUBSTRING('abcde',1,2) 返回结果 ab
 select SUBSTRING('abcde',2,3) 返回结果 bcd
 select SUBSTRING('abcde',1,0) 返回结果为空
 select SUBSTRING('abcde',0,8) 返回结果为abcde,注意后面没有空格了。

(2) If the index of start starts from less than 1 (0 or negative number), the return length is equal to starting from 1, and the intercepted length is length - (the absolute value of (start - 1)), returns null if the difference is negative.

For example: || below represents absolute value calculation

select SUBSTRING('abcde',0,2) 返回结果为 a , 计算公式为SUBSTRING(1,2-|2-1|) 
 select SUBSTRING('abcde',0,-1) 返回错误提示“传递到 substring 函数的长度参数无效”
 select SUBSTRING('abcde',-1,2) 返回结果为空, 计算公式为SUBSTRING(1,2-|-1-1|) 
 select SUBSTRING('abcde',-5,3) 返回结果为空, 计算公式为SUBSTRING(1,3-|-5-1|) 
 select SUBSTRING('abcde',-1,4) 返回结果为ab, 计算公式为SUBSTRING(1,4-|-1-1|) 
 select SUBSTRING('abcde',-2,8) 返回结果为abcde, 计算公式为SUBSTRING(1,8-|-2-1|)

Related learning recommendations: mysql tutorial(video)

The above is the detailed content of How to intercept part of a field using sql statement. For more information, please follow other related articles on the PHP Chinese website!