T-SQL. String functions

--This article is for study notes, ("T_sql" Robert Sheldon Translator: Feng Yu Hui Source: TT China)

---------------------------------------

Function--string function--intercepts the value of a string

Select Name,left (name,12) as a,--intercept from left to right according to the specified string, Format: Ieft (string expression, number of characters)

SUBSTRING (name,9,4) as B,--the "substring" function to extract any part of the string.

--substring (string expression, specifying where to start interception, how long to intercept the string)

Right (name,2) as C,--intercept from the left by the specified string

RTrim (VV) as V-RTrim Delete the space to the right of the string, LTrim remove the space to the left of the string

From Table_1 where ID >1

Function--String function--Modify the value of a string

Select replace (name, ' Mountain ', ' Mtn ') as Replacename,--used to replace a given string value specified in a set of strings as a new string replace (string expression, substituted string value, replacement of a new string value)

Stuff (name,11,2, ' X01 ') as Stuffname,

--"STUFF", which is the function of deleting a set of specified characters at a given location and inserting a new set of characters. The function has four parameters: STUFF (the string expression to be manipulated, the starting position of the string to be deleted, the number of characters to be removed, the string to be inserted)

Reverse (ProductNumber) as Reversenum,--"Reservse" is the role of the given string in reverse order

Stuff (Productnumber,1,2,replicate (0,3)) as Replicatenum,--"replicate (string value to be processed, number of repetitions)", which repeats the specified number of times for a given string value

Productline+space (4) +lower (productline) as Lowerprodline-the "space" function is similar to the "REPLICATE" function, which returns a set of spaces. The function has a parameter that specifies the number of spaces returned

--lower converts uppercase characters in a string to lowercase characters, UPPER converts lowercase characters in a string to uppercase characters

From Table_2 where product_num=7

Function--string function--Converts the value of a string

Select Name+char (9) +str (MakeFlag) as product,--function is "char", which converts an ASCII integer (from 0 to 255) to a character that has only one parameter (an ASCII integer value). This function is handy for inserting control characters into your string, such as the TAB key (ASCII 9), the newline symbol (ASCII 10), or the carriage return (ASCII code 13).

--t-sql also supports the "NCHAR" function, which converts Unicode integers to Unicode characters

--STR (numeric expression to be converted), which converts numeric data to character data, and displays an error message if you connect two columns without converting a bit column. The MakeFlag in the instance is the bit column.

ASCII (MakeFlag) as Ciimakeflag,--converts a single character to an ASCII integer, where the "MakeFlag" value is converted to an integer using the "ASCII" function

Retim (style) as style,--It is important to first use the "RTRIM" function to remove spaces at the end of a string because the "UNICODE" function (like the ASCII function) can only convert one character at a time

Unicode (RTrim (style) as Unicodestyle--unicode (Unicode character expression), which is used to convert a character to a Unicode integer,

From Production.Product where productid=77

Function--string function--Get information about a string

Select Len (description) as Lengthdescriop,--returns the number of characters in the specified string

The function of the charindex (' tecth ', description) as Charindexdescrip,--function is to find the location of a string from the beginning specified in a string, which has three parameters: Charindex (the string to search for, Search range string, where the search begins (optional))

Patindex ('%bike% ', Descripton) as Patindexdescrip,--the function searches the string for the position of the first occurrence of a string. The function has two parameters: PATINDEX (the string to match, the one in which to search)

From Production.productdescription where productdescription_id=231

function--string function--perform "SOUNDEX" Analysis on string

"SOUNDEX" is a system that converts character values into four-character encodings to determine words that are similar in pronunciation. The first character returned by the "SOUNDEX" function is the same as the first string of the target string, and the next three characters are the value codes that are worth the consonants. (a vowel is ignored unless the vowel is the first letter.) )

Select @name1 as name1, @name2 as name2,--Eg:name1=allen,name2=alan

Soundex (@name1) as Soundexname1,soundex (@name2) as Soundexname2,--Soundex (string to parse)

Difference (@name1, @name2) as Soundexdiff--the "defference" function has two parameters, which is the string to be compared, and the return result is an integer representing the degree of difference between the two strings. The returned integer result can be a value between 0 and 4. A value of 0 means that there is almost no similarity between two strings, and a value of 4 means that two strings are very similar

This article is from the "Holly" blog, make sure to keep this source http://5968067.blog.51cto.com/5958067/1698867

T-SQL. String functions