1、字符转化为ASCII,把ASCII转化为字符,注意返回的值是十进制数
-
select ASCII('A'),ASCII('B'),ASCII('a'),ASCII('b'),ASCII('?')
-
-
select CHAR(65),CHAR(66),CHAR(97),CHAR(98),CHAR(63)
2、unicode字符转化为整数,把整数转化为unicode字符
-
select UNICODE('A'),UNICODE('B'),UNICODE('a'),UNICODE('b'),UNICODE('你')
-
-
select NCHAR(65),NCHAR(66),NCHAR(97),NCHAR(98),NCHAR(20320)
3、根据英语发音规则来评价字符串的发音相似度
-
select SOUNDEX('Fleas'),
-
SOUNDEX('Fleece'),
-
SOUNDEX('Peace'),
-
SOUNDEX('Peas')
-
-
select DIFFERENCE('Fleece','Fleas') --返回0~4之间的值,返回值越大,那么越相似
4、字符串大小写转换
-
select UPPER('the company is very large!'),
-
LOWER('THE CORPORATION IS VERY LARGE!')
5、重复一个字符串N次
SELECT REPLICATE('wc ',5)
6、重复一个空格N次
select SPACE(20)
7、获取字符串最左和最右部分
-
SELECT left('the company is very large!',11),
-
right('the company is very large!',6)
8、去掉字符串最左，最右的空格
-
select RTRIM(' the company is very large!'),
-
LTRIM('the company is very large! ')
9、取子串
返回一个字符串在另一个字符串中的起始位置
-
select CHARINDEX('string to find',
-
'this is the bigger string to find something in.',
-
1
-
)
使用通配符查找,返回一个字符串在另一个字符串中的起始位置
-
select PATINDEX('%string%',
-
'this is the bigger string to find something in.'
-
)
10、返回字符串的子串
-
select SUBSTRING('the company is very large!',
-
5,
-
3)
11、返回字符串的字符个数,返回字符串的字节数
-
select len('the company is very large!'),
-
DATALENGTH(N'the company is very large!')
12、把字符串的一部分替换成另一个字符串
-
select REPLACE('the company is very large!',
-
'company',
-
'corporation')
13、把字符串中的一部分填充成另一个字符串
-
select STUFF('the company is very large!',
-
charindex('large','the company is very large!'),
-
LEN('large'),
-
'good'
-
)
14、.返回逆序的字符串
select REVERSE('the company is very large!')
15、把数字转化成字符数据,会四舍五入
要是总长不能满足小数点位数的要求,那么只显示整数部分
-
select STR(-123456.623,
-
7 --这个总长度包括小数点、负号,
-
)
-
-
select STR(123456.653,
-
8, --总长度,会四舍五入
-
2 --数值范围，小数点后面的位数
-
)
16、为输入的UNICODE字符串增加分隔符
在字符串左右加上分隔符
-
select QUOTENAME('abc','"') --分隔符"
-
-
select QUOTENAME('abc','''') --分隔符'
