SQL Server Common functions applicable method (reprint)

Previously, I wanted to record some of the usual functions of SQL, but it has not been implemented ... Hey...

Until today, the substring () function is used, and in C # The starting value of this method is 0, and the starting value in SQL is 1. Silly points are not clear ...

This blog as a function of the use of SQL records, think of where to use where to write where ...

SubString (): the method used to intercept the specified string. The method has three parameters:

Parameter 1: Used to specify the string to manipulate.

Parameter 2: Used to specify the starting position of the string to intercept, with a starting value of 1.

Parameter 3: Used to specify the length to intercept.

Select substring (' abcdef ', 1,3)-    -Return to Abcselect substring (' 123456321 ', 0,2)-    -return 1, first place, best not to do so

Left (): returns the portion of the specified length in the given string. The method has two parameters:

Parameter 1: Used to specify the string to manipulate.

Parameter 2: Used to specify the length of the substring to be returned.

Select Left (' abc123 ', 3)        --Returns Abcselect to left (' right side portion ', 4)    --Returns

Right (): used to return the portion of the specified length in the given string. The method has two parameters:

Parameter 1: Used to specify the string to manipulate.

Parameter 2: Used to specify the length of the substring to be returned.

Select Right (' abc123 ', 3)        --Return 123select to right (' left side portion ', 4)-    -Return part

CharIndex (): used to return the starting position where the specified substring appears in the specified string. Returns 0 if it is not found. The method has two parameters:

Parameter 1: Used to specify the string to be searched.

Parameter 2: Used to specify a string to use as a retrieval.

Select CHARINDEX (' A ', ' 123a123 ')-        -Returns 4select charindex (' abc ', ' 123a123 ')-    -Returns 0select charindex (' abc ', ' 123abc123 ')    --Return 4

Stuff (): used to delete a specified length of character and insert a new character/value at the location of the deletion. The method has four parameters:

Parameter 1: Used to specify the string to manipulate.

Parameter 2: Used to specify the starting position of the character to be deleted.

Parameter 3: Used to specify the length of the character to be deleted.

Parameter 4: Used to specify the new string/value to insert at the location of the deletion.

Select Stuff (' 123abc456 ', 4,3, ' ABC ')-        -return 123abc456select stuff (' 123abc456 ', 1,3, ')-    -Return to abc456, replace with empty string

Len (): The length of the value used to return the specified text. Leading spaces are counted, and trailing spaces are not counted. The method has one parameter:

Parameter 1: Used to specify the text or string to manipulate.

Select Len (' 123 ')-    -Returns 3select len (' string ')-    -Returns 3

difference (): used to return an integer value that indicates the difference between the SOUNDEX values of a two-character expression. (That is, the similarity of two strings) so what is the SOUNDEX value? Just remember, it's the next turn.

The returned values range from 0 to 4:0 means almost different or completely different, and 4 means almost the same or exactly the same. The method has two parameters:

Parameter 1: Used to specify the SOUNDEX value of the first string to be compared.

Parameter 2: Used to specify a second string SOUNDEX value to be compared.

Select difference (' Action ', ' demo ')--        Returns 2select difference (' 123456 ', ' Integer ')-        -Returns 4

Soundex (): used to return the Soundex value of the specified string. Soundex is a speech algorithm, using the pronunciation of English words to calculate the approximate value, the value is composed of four characters, the first character is the English letter, the second three is a number. In the phonetic text sometimes can read but cannot spell the correct word the situation, can use the Soundex to do similar fuzzy matching effect. The fuzzy match here is different from like.

A brief description of the algorithm:

--Replace the English text with the following rule (do not match with the first character, and do not use the value of the English character corresponding to the value 0)    A e h i o u w y, 0    b F p V, 1    c G J K Q S x Z 2    D T-3    L--4    m N-5    R-6

If there are 2 or more letters in the string that have the same corresponding number (for example, J and K), the other is deleted, leaving only 1. Remove the character with the corresponding value of 0, return only the first 4 bytes, not enough with 0 padding.

Select Soundex (' string ')--    returns S215select soundex (' str ')-    -Returns S210

Select Soundex (' 123 ')-    -Returns 0000select soundex (' string ')-    -returns 0000

PS: Characters other than the English character will return 0000, so the second example of the above method difference () returns 4 (the exact same).

Lower (): A string that returns the lowercase form of the specified English string. If it is not an English string, the original value is returned. The method has one parameter:

Parameter 1: Used to specify the string to be converted to lowercase.

Select lower (' ABC ')-        -return abcselect lower (' 123 ')-    -Return 123

Upper (): A string that returns the uppercase form of the specified English string. If it is not an English string, the original value is returned. The method has one parameter:

Parameter 1: Used to specify the string to be converted to uppercase.

Select UPPER (' abc ')-        -return abcselect Upper (' 123 ')-    -Return 123

Ltrim (): used to return a string after a leading space has been deleted. The method has one parameter:

Parameter 1: Used to specify a string to delete leading whitespace operations.

Select LTrim ('        123 ')-        -return 123select ltrim ('        lots of spaces ')-    -Return many spaces select Len ('        123 ')-    -Return 11select Len (LTrim ('        123 '))  --Return 3

Rtrim (): used to return a string after truncating trailing spaces. The method has one parameter:

Parameter 1: A string that specifies the trailing white space operation to be truncated.

Select RTrim (' 123         ')-        -return 123select len (RTrim (' 123         '))-  -Return 3

Replace (): replaces all occurrences of the second given string expression in the first string expression with a third expression. The method has three parameters:

Parameter 1: Used to specify the string to manipulate, which is the string to be matched.

Parameter 2: Used to specify the string to be matched.

Parameter 3: Used to specify a string to use as a replacement for a match.

--Replace ABC with XXX Select replace (' 123abc456 ', ' abc ', ' xxx ')        --return 123xxx456--replace match with an empty string select replace (' 123abc456 ', ' ABC ', ')    --Return 123456

Unicode (): returns the integer value of the first character of a specified character or string, based on the Unicode standard. The method has one parameter:

Parameter 1: Used to specify the character or string to manipulate.

Select Unicode (' a ')-        -Returns 97select Unicode (' abc ')-    -Returns 97, returning only the first character

NChar (): returns the Unicode character of the specified integer value code according to the Unicode standard. The method has one parameter:

Parameter 1: Specifies the integer value code for a Unicode standard.

Select NCHAR-    -return aselect nchar (+)-    -Return A

Char (): Converts the value of the specified int type to an ASCII code. The method has one parameter:

Parameter 1: Specify a numeric value of type int with a range of values from 0 to 255. Returns null to indicate that an integer expression is not within this range.

Select Char    --Return Cselect char (9)-    -Tab character select char (TEN)-    -newline character Select char (+)-    -carriage return

ASCII (): Returns the ASCII code value of the first character of a specified character or string. The method has one parameter:

Parameter 1: Used to specify the character or string to manipulate.

Select ASCII (' a ')-    -Returns 65select ASCII (' a ')-    -Returns 97

Ps:ascii () is used to manipulate single-byte, double-byte use Unicode (). Full-width characters are double-byte, and Chinese characters are double-byte.

Example:

Select Unicode (' string ')-    -Returns 23383select nchar (' 23383 ')--    Returns the word select char (' 23383 ')-    -Returns nullselect ASCII (' String ')-    -return 215select nchar (' 215 ')--    return xselect char (' 215 ')-    -return nullselect ASCII (' Kan ')-    -Return 75sele CT nchar (' a ')--    return Kselect char (' the ")-    -Return K

Str (): returns the equivalent string of a specified numeric value. The method has three parameters:

Parameter 1: Specify the numeric value to manipulate.

Parameter 2: Specifies the length of the string to return. The default value is 10, which means that the default length of 10 is not specified and is insufficient to fill with empty characters.

Parameter 3: The number of decimal digits to return. The default value is 0, which means that no decimals are reserved, less than the specified number of digits, supplemented with 0.

Select STR (123.10)-    -return        123select len (str (123.10))-    -return 10select str (123.10,5)-    -return   123select Len (str (123.10,5))    --Return 5select str (123.10,20,5)--return            123.10000select len (str (123.10,20,5))-    -return 20

space (): a space string that returns the length of a specified numeric value. The method has one parameter:

Parameter 1: Specifies the length of the returned space string.

Select ' 1 ' +space (3) + ' 2 '        --return 1   2select len (' 1 ' +space (3) + ' 2 ')    --Return 5

Reverse (): used to invert the specified string and invert it by a single character. The method has one parameter:

Parameter 1: Specifies a string that requires a reverse operation.

Select reverse (' 123 ')--    return 321select reverse (' abc ')-    -Return to CBA

Replicate (): used to return a string that repeats a specified number of times for a specified string. The method has two parameters:

Parameter 1: Used to specify the string to manipulate.

Parameter 2: Used to specify the number of times the string repeats. If 0, returns an empty string, or null if it is a negative number.

Select Replicate (' 123 ', 3)-    -return 123123123select replicate (' a ', 3)-        -return AAA

Quotename (): returns a Unicode string that returns a valid string identifier, based on the specified delimiter. The method has two parameters:

Parameter 1: Used to specify the string to manipulate.

Parameter 2: Used to specify a delimiter, either single quotation mark ('), opening parenthesis ([), closing parenthesis (]), left and right brackets ([]), or double quotation marks ("). If not specified, left and right brackets are used by default.

Select QuoteName (' 123 ', ' [] ')-    -Return to [123]select QuoteName (' 123 ', ' ')-    -return ' 123 ' Select QuoteName (' 123 ', ' "')        --Return "123" Select QuoteName (' 12[]3 ', ' [] ')    --return [12[]]3], double the closing parenthesis to indicate the escape character.

Patindex (): returns the starting position of the first occurrence of the specified pattern in the specified string expression, and returns 0 if not found. The method has two parameters:

Parameter 1: Specify the string to match and the matching pattern.

Parameter 2: Specifies the string expression to be matched.

Select Patindex (' abc ', ' 123abc456abc789 ')-    -return 0select Patindex (' abc ', ' abc ')-    -return 1select Patindex (' abc% ', ' 123abc456abc789 ')    --Return 0select Patindex ('%abc% ', ' 123abc456abc789 ')-    -return 4select Patindex ('%[abc]% ', ' 123abc456abc789 ')    --matches the position of the first occurrence of the string ABC, returns 4select Patindex ('%[^abc]% ', ' 123abc456abc789 ')    --matches not equal to the characters a, B, c Where other characters first appear, return 1select Patindex ('%[^abc]% ', ' b123abc456abc789 ')    -matches the position of the first occurrence of other characters not equal to the characters a, B, C, and returns 2

parsename (): returns the specified part of the object name. The parts of the object that can be retrieved include the object name, owner name, database name, and server name. You can actually use it to intercept strings. The method has two parameters:

Parameter 1: The name of the object to retrieve the specified part of. This name can consist of four parts: server name, database name, owner name, and object name.

Parameter 2: The part of the object to return. Can only be values from 1 to 4. 1 = object name; 2 = schema name; 3 = database name; 4 = server name

Select ParseName (' server.dbo.dbTest.table ', 1) tablename,       parsename (' server.dbo.dbTest.table ', 2) dbname,       ParseName (' server.dbo.dbTest.table ', 3) SchemaName,       parsename (' server.dbo.dbTest.table ', 4) servername

This interception method is similar to string segmentation, but it is intercepted in reverse, so it can be used to intercept the string in some degree. Only the decimal (.) segmentation is supported, and only four bits are supported.

Select ParseName (' 192.168.1.1 ', 4) col1,       parsename (' 192.168.1.1 ', 3) col2,       parsename (' 192.168.1.1 ', 2) col3,       parsename (' 192.168.1.1 ', 1) col4

Getdate (): Gets the current date time.

Select GETDATE ()

Year (): Gets the years of the specified date expression. The method has one parameter:

Parameter 1: Specifies the date expression to be manipulated.

Select year (' 2016-01-01 ')-    -Returns 2016select year (' 2017-01-01 12:30:30 ')-    -returns 2017select year (GETDATE ())    - -Return 2017

Month (): Gets the months of the specified date expression. The method has one parameter:

Parameter 1: Specifies the date expression to be manipulated.

Select month (' 2016-01-01 ')-    -return 1select month (' 2017-10-01 12:30:30 ')--    return 10select month (GETDATE ())-    -return 5

Day (): Gets the date of the specified day expression. The method has one parameter:

Parameter 1: Specifies the date expression to be manipulated.

Select Day (' 2016-01-01 ')--return    1select day (' 2017-10-03 12:30:30 ')-    -return 3select Day (getdate ())-    -Return 11

Isdate (): used to determine whether the specified string expression is a date. Returns 1, indicating that the specified string expression is a date. Returns 0, indicating that the specified string expression is not a date. The method has one parameter:

Parameter 1: Used to specify the string expression to be manipulated.

Select IsDate (' 10/30/2017 ')        --month/day/year        return 1select isdate (' 30/10/2017 ')        --day/month/year        return 0select IsDate (' 2017 /10/30 ')--        year/month/day        return 1select isdate (' 02/29/2017 ')        --for judging leap years, since 2017 is not a leap year, so there is no     return 0select isdate (' 2017-01-01 ')        --Return 1select isdate (' 2017-01-01 10:10:10 ')--    return 1select isdate (' 01-01-2017 ')-        -Return 1select IsDate (' 01-01-2017 10:10:10 ')    --Return 1

datename (): A string that returns the portion of the specified date for the specified date. The method has two parameters:

Parameter 1: Specifies a string that returns the specified part of the specified date. Can be a date part or an abbreviation in.

Parameter 2: Specifies the date string to manipulate.

Select Datename (year, ' 2017-01-01 10:10:10 ')--    return 2017select datename (yyyy, ' 2017-01-01 10:10:10 ')-    -return 2017select datename (month, ' 2017-01-01 10:10:10 ')-    -return 01select datename (mm, ' 2017-01-01 10:10:10 ')-    -return 01    Select Datename (quarter, ' 2017-05-02 10:10:10 ')-        -represents the first quarter of the year to        return 2select datename (weekday, ' 2017-02-02 10:10:10 ')        --Indicates the week of the week        returns Thursday select Datename (Week, ' 2017-02-02 10:10:10 ')-        -Indicates the first week of the year to        return 5select Datename (DayOfYear, ' 2017-02-02 10:10:10 ')        --the first day of the year        returns 33select datename (hh, ' 2017-01-01 10:30:25 ')    --Return 10

Datepart (): returns the integer of the part of the specified date for the specified date. The Datepart () method works similarly to Datename (), except that the Datename () method returns a string, and the Datepart () method returns an integer value. The method has two parameters:

Parameter 1: Specifies a string that returns the specified part of the specified date. Can be a date part or an abbreviation in.

Parameter 2: Specifies the date string to manipulate.

Select DATEPART (year, ' 2017-01-01 10:10:10 ')--    return 2017select datepart (yyyy, ' 2017-01-01 10:10:10 ')-    -return 2017select datepart (month, ' 2017-01-01 10:10:10 ')-    -return 1select datepart (mm, ' 2017-01-01 10:10:10 ')-    -return 1    Select DatePart (quarter, ' 2017-05-02 10:10:10 ')-        -represents the first quarter of the year to        return 2select datepart (weekday, ' 2017-02-02 10:10:10 ')--the day of the week, the day        of the week, Sunday    returns 5select datepart (week, ' 2017-02-02 10:10:10 ') for the first of the week-        the week of the year        return 5select datepart (dayofyear, ' 2017-02-02 10:10:10 ')        --the day of the year        returns 33select datepart (hh, ' 2017-01-01 10:30:25 ')    --Return 10

SQL Server Common functions applicable method (reprint)