An example of the usage of SUBSTRING functions in SQL Server _mssql

The substring function in SQL is used to grab a portion of a field data. The name of this function is not exactly the same in different repositories:

• Mysql:substr (), SUBSTRING ()
• Oracle:substr ()
• SQL server:substring ()

The substring function in SQL is used to intercept a portion of a field data.

For example, we need to extract the ' Abd ' from the string ' Abdcsef ', which can be implemented using substring:

select substring('abdcsef',1,3)

Results:

'abd'

The number ' 1 ' in parentheses indicates that the starting position of the interception begins with the first character of the string, and that ' 3 ' indicates that the length of the string obtained after the interception is 3 characters.

This is the most basic grammar of ' substring ', of course, our needs sometimes become more complex, such as the following example:

We just want the room number in ' Roomno ' and find that the starting character position is not fixed, and the length of the room number we need is not fixed.

At this point, we can use the ' charindex ' function to easily handle, which is used to locate a particular character in the string position, that is, the function

The resulting result is a number that represents the position of a particular character. Execute the following code:

Select Room_stand=substring (Roomno,charindex, Roomno) +1,charindex (' Room ', Roomno)-charindex (' Yuan ', Roomno)-1) 
From Property_room

Results:

The above is a small set of SQL Server to introduce the use of the SUBSTRING Function example analysis, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!