Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to intercept a part of a field in the sql sentence". In the daily operation, I believe that many people have doubts about how to intercept a part of a field in the sql statement. The editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt of "how to intercept a part of a field in the sql sentence". Next, please follow the editor to study!

In sql, you can use the SUBSTRING statement to intercept a portion of a field with the syntax format "SUBSTRING (string, start position, intercept length)". The substring function in SQL is used to capture a part of a field data.

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

SUBSTRING (expression, start, length)

Parameters.

Expression

A string, binary string, text, image, column, or expression that contains a column. Do not use expressions that contain aggregate functions.

Start

An integer or an expression that can be implicitly converted to int that specifies the starting position of the substring, with the index starting at 1.

Length

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

Return value

1. If expression is a supported binary data type, binary data is returned, which we will not discuss for the time being.

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

(1) if the index of start starts at 1, the string is intercepted from the first character of the expression, from 2 to the second character of the expression, and so on.

For example:

Select SUBSTRING ('abcde',1,2) return result ab select SUBSTRING (' abcde',2,3) return result bcd select SUBSTRING ('abcde',1,0) return result is empty select SUBSTRING (' abcde',0,8) return result is abcde, notice that there are no spaces after it.

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

For example: the following | | indicates the calculation of absolute value

The result returned by select SUBSTRING ('abcde',0,2) is a, and the calculation formula is SUBSTRING (1Magazine 2-| 2-1 |) select SUBSTRING (' abcde',0,-1). The error message "invalid length parameter passed to substring function" select SUBSTRING ('abcde',-1,2) returns empty result, while the formula SUBSTRING (1Magne2-|-1-1 |) select SUBSTRING (' abcde',-5,3) returns empty result. The calculation formula is SUBSTRING (1Magazine 3-|-5-1 |) select SUBSTRING ('abcde',-1,4). The returned result is ab, and the formula is SUBSTRING (1Magazine 4-|-1-1 |) select SUBSTRING (' abcde',-2,8). The returned result is abcde, and the formula is SUBSTRING (1Magazine 8-|-2-1 |). The study on "how to intercept a part of a field in a sql statement" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.