Usage of substring in SQL Server

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

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

SUBSTRING ('abdcsef',1,3)    

Results:

'abd' 

The number ' 1 ' in parentheses indicates that the starting position of the intercept starts with the first character of the string, and ' 3 ' means that the resulting string length is 3 characters.

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

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

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

The result is a number used to represent a particular character position. Execute the following code:

Select Room_stand=SUBSTRING (Roomno,Charindex‘Yuan ', Roomno)+1,charindex (' room ', Roomno)-charindex (' Yuan ', Roomno )-1) from property_roomwhere Roomno like '% unit%'   

Results:

Usage of substring in SQL Server