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:

Select 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:

SelectRoom_stand=substring(Roomno,charindex('Yuan', Roomno)+1,charindex('Room', Roomno)-charindex('Yuan', Roomno)-1)  fromProperty_roomwhereRoomno like '% Unit% room %' 

Results:

Usage of substring in SQL Server