5. SQL Basic collation (string function)

String function ASCII

Returns the ASCII encoding of the first letter of the string

Select ASCII (' name ')

Select ASCII (name) from Xuesheng

Select *from Xuesheng where ASCII (name) >=200

CHAR

--Convert the ASCII code to the corresponding character

Select CHAR (13)--Enter

CHARINDEX

Searches for another expression in one expression and returns its starting position (returns ' 0 ' if not found)

Select CHARINDEX (' EFG ', ' abcdefgh ')

Select CHARINDEX (' 8 ', math) from Xuesheng

Concat

Returns a string that is the result of concatenating two or more string values like select ' abc ' + ' def ' + ' Ghi '

Used in version *2012

SOUNDEX

Returns a four-character code (SOUNDEX) that evaluates the similarity of two strings.

Select SOUNDEX (' abc '), SOUNDEX (' ABCD ')

Difference

Returns an integer value that indicates the difference between the SOUNDEX values of a two-character expression.

Select difference (' abc ', ' ABCD ')

Left

Select Left (' ABCdef ', 3)

Select Left (math,3) from Xuesheng

LEN

Returns the length of a string, followed by a space that does not count

Select LEN (' 123 ')

LOWER

Convert uppercase letters to lowercase

Select lower (' ABCDE ')

LTRIM

Remove the left space, which is the front space of the string, equivalent to TrimStart in. Net

Select LTRIM (' abc E ')

NCHARPATINDEX

Returns the starting position of the first occurrence of the pattern in the specified expression, or zero if the pattern is not found in all valid text and character data types. (% except in the first or the bottom can be found outside must add, if the pattern in the first plus% to the bottom, if the pattern in the bottom plus% to the first) syntax: PATINDEX ('%pattern% ', expression)

Select PATINDEX ('%abc% ', ' 1abcdef1 ')

REPLACE

Finds a data and replaces it. (no changes, just display)

Syntax: REPLACE (string_expression, String_pattern, string_replacement)

Select REPLACE (' ABCDE ', ' C ', ' F ')

Select REPLACE (Name, ' King ', ' Zhang ') from Xuesheng

Update Xuesheng Set name = REPLACE (name, ' king ',' Zhang ')

(Can be changed)

REPLICATE

Copy, and can be copied 0--n times, equivalent to the first cut after the paste process

Select REPLICATE (' abc ', 0)

Select REPLICATE (' abc ', 3)

REVERSE

Flip

Select REVERSE (' abcdef ')

Right

(relative to left)

RTRIM

(relative to LTrim, go to the right of the space)

SPACE

Returns a string of repeated spaces

Select ' A ' +space (3) + ' B '

Str

Converts a decimal number to a string , length is a string, and the third parameter is a few after the decimal point (the length cannot exceed the length specified by the intermediate parameter, otherwise the display * * *, after the decimal point can not be more than the length of the middle parameter, otherwise not shown) the length of the front is displayed as a space , the fractional part is shown as ' 0 '

Syntax: STR (float_expression [, length [, decimal]])

Select STR (4467.2345,8,3)

STUFF

Deletes a specified length of character at the beginning of the specified index, and then inserts the specified character at the specified index

Syntax: STUFF (character_expression, start, length, replacewith_expression)

Select Stuff (' abc123 ', 3,2, ' HelloWorld ')

SUBSTRING

Returns part of a character, binary, text, or image expression in SQL Server.

Syntax: SUBSTRING (expression, start, length)

Select SUBSTRING (name,1,1) from Xuesheng

UPPER

Change the lowercase letter of a string to uppercase

Select UPPER (' 123helloworld ')

5. SQL Basic collation (string function)