20101102--sql String Functions

--------------------string function---------------------------ASCII returns the ASCII encoding of the first letter of a stringSelect ASCII('W')Select ASCII('a')Select ASCII('s')Select ASCII('D')Select ASCII(name) fromXueshengxinxiSelect * fromXueshengxinxiwhere ASCII(name)> $--query for ASCII code greater than 200 in name--Char converts ASCII code to the corresponding character charSelect CHAR(101)Select CHAR(age) fromXueshengxinxi--INDEX--CHARINDEX finds a string that returns the index of the first letter of a qualifying string, starting at 1 and returning 0 is not foundSelect CHARINDEX('%','qwe#r1%4tyyuiopasdfghjklzxcvbnm')Select CHARINDEX('2', test2) fromXueshengxinxi--PATINDEX returns the starting position of the first occurrence of a pattern in the specified expression, or zero if the pattern is not found in all valid text and character data types. --wildcard meaning--% contains any string of 0 or more characters. --_ any single character. --[] Specifies any single character within a range (for example [a-f]) or a collection (for example [abcdef]). --[^] any single character that is not within a specified range (for example [^a-f]) or a collection (for example [^abcdef]). Select PATINDEX('%%%','[email protected] #q78dwq6%478')--string concatenation + numberSelect 'ABC'+'def'+'GH'--difference returns the similarity,Select difference('ABCDE','2')--left returns the number of characters in the string that are specified in the beginning. LeftSelect  Left('ABCDE',3)--the number on the back is a few to intercept.Select  Left(Test2,2) fromXueshengxinxi--right returns the specified number of characters in the string starting from the left. --LEN Returns the number of characters in the specified string expression, which does not contain trailing spaces. Select LEN('1234')--LOWER converted to lowercaseSelect LOWER('ASDFG')--UPPER Convert capitalization--LTRIM removes the left-hand space in the string, and the. NET TrimStartSelect LTRIM('1')--RTRIM Remove the right space in the string,--Replace replaces, the first parameter is the target position, the second argument is the character segment to replace, and the third is the string to replace. Displays only the results, which do not affect the database data .Select REPLACE(Test,'8','two points') fromXueshengxinxi--REPLICATE Copy, can replicate 0-n times, equivalent to cut and then copySelect REPLICATE('qwe',3)--REVERSE FlipSelect REVERSE(name) fromXueshengxinxi--SOUNDEX Returns a four-character code (SOUNDEX) that evaluates the similarity of two stringsSELECT SOUNDEX('Smith'),SOUNDEX('Smythe')--Space whitespace parameter is a few spaces to enter a fewSelect 'a'+SPACE(Ten)+'b'--STR returns the character data converted from numeric data. Parameter 1 is the original value, the parameter 2 is the length of the string (including the decimal point), the parameter 3 is a fewSelect STR(213.450,5,2)--STUFF inserts a string into another string. Parameter 1 The original string, parameter 2 specifies the start position of the delete and insert, Parameter 3 specifies the number of characters to delete, and parameter 4 specifies the string to insertSelect STUFF('Abc123gh',4,3,'def')--SUBSTRING The string at the specified length from the specified index. Parameter 1 raw string, parameter 2 specify index, parameter 2 intercept string lengthSelect SUBSTRING(Name,2,1) fromXueshengxinxi

20101102--sql String Functions