string manipulation in SQL Drip 33-sql

Text: string manipulation in SQL Drip 33-sql

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

Returns a string consisting of repeated spaces

Space (integer_expression)    integer_expression A positive integer that indicates the number of spaces. If integer_expression is negative, an empty string is returned.  Select'A'+space(2)+' B '

Take a substring

substring(String,start_position,length) can take substrings of any length from any location, Left(String,length) to take substrings from the left Right(string,length) starting from the right to take substringsSelect substring('helloworld!',6,6)Select  Left('helloworld!',5)Select  Right('helloworld!',6)

String substitution

Replace (string, strings to be replaced, strings to replace) 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.  Selectstuff('abcdefg',1,6,'   ') result is: Hello g

Repeats a string value at a specified number of times

Replicate (String_Expression, integer_expression) Select Replicate ('abc',4) The result is: ABCABCABCABC

Returns the starting position of the specified expression in the string

charindex (expression1, expression2  , start_location) or charindex (expression1, expression2) expression1 in expression2 Start position in selectcharindex('H','ellohworld  The result is:5

Returns the starting position of the first occurrence of the pattern in the specified expression, returning the first occurrence of a pattern in the specified expression;

Patindex ('%pattern%', 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'ForncharOrnvarcharan expression. Select Unicode('a') The result is: theSelect Unicode('ABC') The result is: the

Returns character data converted from numeric data
STR (float_expression, length, decimal)
Float_expression An expression of an approximate numeric (float) data type with a decimal point.
Length total. It includes decimal points, symbols, numbers, and spaces. The default value is 10.
The decimal place to the right of the decimal point. Decimal must be less than or equal to 16. If decimal is greater than 16, the result is truncated to the 16-bit to the right of the decimal point.

Select Str(123.436,2),Len(Str(123.436,2))//When an expression exceeds a specified length, the string is returned for the specified length**Select Str(123.436),Len(Str(123.436)),Str(123.436,6),Len(Str(123.436,6)),Str(123.436,6,1),Len(Str(123.436,6,1))//An expression consisting of six numbers and a decimal point is converted to a string with six positions. The decimal portion of the number is rounded to a decimal place. Select Str(1234.436),Len(Str(1234.436)),Str(1234.436,6),Len(Str(1234.436,6)),Str(1234.436,6,1),Len(Str(1234.436,6,1))

Get the ASCII code of the character
ASCII () is used to get the ASCII code of a character, it has only one parameter, if the argument is a string, then take the first character ASCII code

Select ASCII ('H') Select ASCII ('helloworld! ')

Get a character corresponding to the ASCII code number

Char (integer_expression)  0 255 NULL values.  SelectChar(.)

Returns a Unicode character that returns a specified integer code

nchar (integer_expression)  0 65535 NULL .  Selectnchar(+)

Returns a Unicode string with delimiters, in which the input string becomes a valid SQL Server delimited identifier.

QuoteName('character_string') character_string must not exceed -a character. More than -Characters of the input will returnNULL. Select QuoteName('Abc[aa]def') The result is:[abc[]]DEF] Please note that the string ABC[]the right bracket in def has two, which indicates the escape character. Select QUOTENAME('abcdef'," '")--delimiter is two single quotation marks--' abcdef 'Select QUOTENAME('abcdef')--delimiter is]--[abcdef]Select QUOTENAME('abcdef','{}')--delimiter is}--{abcdef}

Pronunciation Matching degree
Sometimes we don't know the spelling of a person's name, but we know his pronunciation, and then we can make a matching test of the pronunciation.
Soundex () is used to calculate the pronounced characteristics of a string, which is a four-character string, the first character of the feature is always the first character in the initial string, and then a three-digit numeric value.
Select Sname, Soundex (sname) from student
The meaning of the pronounced eigenvalues is very complex, and it is cumbersome to analyze the pronunciation similarity of two strings based on the two pronounced eigenvalues.
You can use difference () to simplify the pronunciation similarity comparison of two strings, which can calculate the pronounced eigenvalues of two strings, and compare them,
It then returns a value between 0~4 to reflect the pronunciation similarity of two strings, and the larger the value, the greater the pronunciation similarity of the two strings.
Select Sname,soundex (sname), Difference (sname, ' Herry ') from Stu

string manipulation in SQL Drip 33-sql