Calculating string Lengths
Len () is used to calculate the length of a string
Select sname,len from student
Convert string to large, lowercase
Lower () is used to convert a string to lowercase, and upper () is used to convert a string to uppercase
Select Lower ('I AM A STUDENT! ' )SelectUpper('I am a student! ')
to truncate the left and right spaces of a string
LTrim () is used to remove a space to the left of a string, and RTrim () is used to remove the space to the right of a string
Declare @str varchar( -)Set @str='There are spaces on my left! 'Select @str asInitial character,Len(@str) asInitial length,LTrim(@str) asExisting characters,Len(LTrim(@str)) asExisting length
substring
substring (string,start_position,length) can take substrings of any length from any location,
Left (string,length) Take substring
Right (string,length) from the left to the substring
select substring ( '
String substitution
Replace (string, strings to be replaced, strings replaced)
Select Replace ('helloworld! ','o','e') Result: hellewerld!
Returns the inverse value of a string value
Reverse (string_expression)
Select Reverse ('abc') Result: CBA
Deletes a specified length of character and inserts another set of characters at the specified starting point
Stuff (character_expression, start, length, character_expression)
Start an integer value that specifies the start position of the delete and insert.
Length An integer that specifies the number of characters to delete.
Select Stuff ('abcdefg',1,6,') Result: Hello g
Repeats a string value at a specified number of times
Replicate (String_Expression, integer_expression)
Select Replicate ('abc',4)
Returns the starting position of the specified expression in the string
CHARINDEX (expression1, expression2, start_location) or charindex (expression1, expression2)
expression1 start position in expression2
Select charindex ('H','ellohworld') The result is:5
Returns the starting position of the first occurrence of the pattern in the specified expression
Patindex ('%pattern% ', expression) returns the starting position of the first occurrence of a pattern in a specified expression;
If the pattern is not found in all valid text and character data types, zero is returned.
Select Patindex ('%hello%','worldhello') The result is:6
Returns an integer value of the first character of an input expression
Unicode (' ncharacter_expression ') ' ncharacter_expression ' expression for nchar or nvarchar Expression
Select Unicode ('a') The result isas follows: The result of the ". *Unicode('abc') is:
Common methods for SQL Server