T Sql 简明教程

T-SQL - String Functions

MS SQL Server 字符串函数可应用于字符串值，或将返回字符串值或数字数据。

MS SQL Server String functions can be applied on string value or will return string value or numeric data.

以下是字符串函数及其示例列表。

Following is the list of String functions with examples.

ASCII()

字符表达式的输出是 ASCII 码值。

Ascii code value will come as output for a character expression.

Example

以下查询将给出给定字符的 ASCII 码值。

The following query will give the Ascii code value of a given character.

Select ASCII ('word')

CHAR()

输出是给定 ASCII 码值或整数对应的字符。

Character will come as output for given Ascii code or integer.

Example

以下查询将给出给定整数对应的字符。

The following query will give the character for a given integer.

Select CHAR(97)

NCHAR()

输出是给定整数对应的 Unicode 字符。

Unicode character will come as output for a given integer.

Example

以下查询将给出给定整数对应的 Unicode 字符。

The following query will give the Unicode character for a given integer.

Select NCHAR(300)

CHARINDEX()

给定字符串表达式中给定搜索表达式的起始位置将作为输出。

Starting position for given search expression will come as output in a given string expression.

Example

以下查询将给出给定字符串表达式“KING”中字符“G”的起始位置。

The following query will give the starting position of 'G' character for given string expression 'KING'.

Select CHARINDEX('G', 'KING')

LEFT()

给定字符串的由左起指定数量字符的左侧将显示为给定字符串的输出。

Left part of the given string till the specified number of characters will come as output for a given string.

Example

以下查询将给定字符串“WORLD”中指定的 4 个字符数量的“WORL”字符串。

The following query will give the 'WORL' string as mentioned 4 number of characters for given string 'WORLD'.

Select LEFT('WORLD', 4)

RIGHT()

给定字符串的由右起指定数量字符的右侧将显示为给定字符串的输出。

Right part of the given string till the specified number of characters will come as output for a given string.

Example

以下查询将给定字符串“INDIA”中指定的 3 个字符数量的“DIA”字符串。

The following query will give the 'DIA' string as mentioned 3 number of characters for given string 'INDIA'.

Select RIGHT('INDIA', 3)

SUBSTRING()

基于给定字符串的起始位置值和长度值的字符串部分将显示为给定字符串的输出。

Part of a string based on the start position value and length value will come as output for a given string.

Example

以下查询将给定字符串“WORLD”、“INDIA”和“KING”分别指定的 (1,3)、(3,3) 和 (2,3) 起始和长度值显示为“WOR”、“DIA”和“ING”字符串。

The following queries will give the 'WOR', 'DIA', 'ING' strings as we mentioned (1,3), (3,3) and (2,3) as start and length values respectively for given strings 'WORLD', 'INDIA' and 'KING'.

Select SUBSTRING ('WORLD', 1,3)
Select SUBSTRING ('INDIA', 3,3)
Select SUBSTRING ('KING', 2,3)

LEN()

给定字符串表达式的字符数量将显示为输出。

Number of characters will come as output for a given string expression.

Example

以下查询将给出“HELLO”字符串表达式的 5。

The following query will give the 5 for the 'HELLO' string expression.

Select LEN('HELLO')

LOWER()

小写字符串将显示为给定字符串数据的输出。

Lowercase string will come as output for a given string data.

Example

以下查询将针对“SQLServer”字符数据给出“sqlserver”。

The following query will give the 'sqlserver' for the 'SQLServer' character data.

Select LOWER('SQLServer')

UPPER()

大写字符串将显示为给定字符串数据的输出。

Uppercase string will come as output for a given string data.

Example

以下查询将针对“SqlServer”字符数据给出“SQLSERVER”。

The following query will give the 'SQLSERVER' for the 'SqlServer' character data.

Select UPPER('SqlServer')

LTRIM()

删除前导空格后，字符串表达式将显示为给定字符串数据的输出。

String expression will come as output for a given string data after removing leading blanks.

Example

以下查询将针对“ WORLD”字符数据给出“WORLD”。

The following query will give the 'WORLD' for the ' WORLD' character data.

Select LTRIM('   WORLD')

RTRIM()

删除尾随空格后，字符串表达式将显示为给定字符串数据的输出。

String expression will come as output for a given string data after removing trailing blanks.

Example

以下查询将针对“INDIA ”字符数据给出“INDIA”。

The following query will give the 'INDIA' for the 'INDIA ' character data.

Select RTRIM('INDIA   ')

REPLACE()

用指定字符替换指定字符的所有实例后，字符串表达式将显示为给定字符串数据的输出。

String expression will come as output for a given string data after replacing all occurrences of specified character with specified character.

Example

以下查询将针对“INDIA”字符串数据给出“KNDKA”字符串。

The following query will give the 'KNDKA' string for the 'INDIA' string data.

Select REPLACE('INDIA', 'I', 'K')

REPLICATE()

重复字符串表达式将显示为给定字符串数据带有指定次数的输出。

Repeat string expression will come as output for a given string data with specified number of times.

Example

以下查询将为“WORLD”字符串数据提供“WORLDWORLD”字符串。

The following query will give the 'WORLDWORLD' string for the 'WORLD' string data.

Select REPLICATE('WORLD', 2)

REVERSE()

反向字符串表达式将成为给定字符串数据的输出。

Reverse string expression will come as output for a given string data.

Example

以下查询将为“WORLD”字符串数据提供“DLROW”字符串。

The following query will give the 'DLROW' string for the 'WORLD' string data.

Select REVERSE('WORLD')

SOUNDEX()

返回四字符（SOUNDEX）代码以评估两个给定字符串的相似度。

Returns four-character (SOUNDEX) code to evaluate the similarity of two given strings.

Example

以下查询将为“Smith”、“Smyth”字符串提供“S530”。

The following query will give the 'S530' for the 'Smith', 'Smyth' strings.

Select SOUNDEX('Smith'), SOUNDEX('Smyth')

DIFFERENCE()

给定两个表达式将提供整数值作为输出。

Integer value will come as output of given two expressions.

Example

以下查询将为“Smith”、“Smyth”表达式提供 4。

The following query will give the 4 for the 'Smith', 'Smyth' expressions.

Select Difference('Smith','Smyth')

Note - 如果输出值为 0，则表示给定的 2 个表达式之间相似性较弱或没有相似性。

Note − If the output value is 0 it indicates weak or no similarity between give 2 expressions.

SPACE()

字符串将以指定数量的空间作为输出。

String will come as output with the specified number of spaces.

Example

以下查询将提供“I LOVE INDIA”。

The following query will give the 'I LOVE INDIA'.

Select 'I'+space(1)+'LOVE'+space(1)+'INDIA'

STUFF()

在从起始字符替换到指定字符长度后，字符串表达式将作为给定字符串数据的输出出现。

String expression will come as output for a given string data after replacing from starting character till the specified length with specified character.

Example

以下查询将为“ABCDEFGH”字符串数据提供“AIJKFGH”字符串，其中起始字符和长度分别为 2 和 4，“IJK”为指定的 target 字符串。

The following query will give the 'AIJKFGH' string for the 'ABCDEFGH' string data as per given starting character and length as 2 and 4 respectively and 'IJK' as specified target string.

Select STUFF('ABCDEFGH', 2,4,'IJK')

STR()

字符数据将作为给定数字数据的输出出现。

Character data will come as output for the given numeric data.

Example

根据指定的长度为 6 和 decimal 为 2，以下查询将为给定的 187.369 提供 187.37。

The following query will give the 187.37 for the given 187.369 based on specified length as 6 and decimal as 2.

Select STR(187.369,6,2)

UNICODE()

给定表达式的第一个字符将作为输出提供整数值。

Integer value will come as output for the first character of given expression.

Example

以下查询将为“RAMA”表达式提供 82。

The following query will give the 82 for the 'RAMA' expression.

Select UNICODE('RAMA')

QUOTENAME()

给定的字符串将以指定的定界符作为输出出现。

Given string will come as output with the specified delimiter.

Example

以下查询将为给定的“RAMA”字符串提供“RAMA”，因为我们将双引号指定为定界符。

The following query will give the "RAMA" for the given 'RAMA' string as we specified double quote as delimiter.

Select QUOTENAME('RAMA','"')

PATINDEX()

需要从给定的表达式中指定“I”位置作为第一次出现的起始位置。

Starting position of the first occurrence from the given expression as we specified 'I' position is required.

Example

以下查询将为“INDIA”提供 1。

The following query will give the 1 for the 'INDIA'.

Select PATINDEX('I%','INDIA')

FORMAT()

给定的表达式将以指定格式作为输出。

Given expression will come as output with the specified format.

Example

以下查询将针对 getdate 函数按指定格式提供“2015 年 11 月 16 日，星期一”，其中“D”表示星期名。

The following query will give the ' Monday, November 16, 2015' for the getdate function as per specified format with 'D' refers weekday name.

SELECT FORMAT ( getdate(), 'D')

CONCAT()

连接给定的参数值后，将作为输出提供单个字符串。

Single string will come as output after concatenating the given parameter values.

Example

以下查询将针对给定的参数提供“A,B,C”。

The following query will give the 'A,B,C' for the given parameters.

Select CONCAT('A',',','B',',','C')