SQL 10 函数 2 字符串函数

2.1 计算字符串长度

MySQL, Oracle:

SELECT FName, LENGTH(FName) FROM T_Person

 

2.2 字符串转换为小写

MySQL, Oracle:

SELECT FName, LOWER(FName) FROM T_Person

 

2.3 字符串转换为大写

MySQL, Oracle:

SELECT FName, UPPER(FName) FROM T_Person

 

2.4 去除字符串左侧空格

SELECT FName, LTRIM(FName) FROM T_Person

展示效果的SQL：

MySQL:

SELECT FName, LTRIM('   123abc   '), LENGTH(LTRIM('   123abc   '))

Oracle:

SELECT FName, LTRIM('   123abc   '), LENGTH(LTRIM('   123abc   ')) FROM DUAL

 

2.5 去除字符串右侧空格

SELECT FName, RTRIM(FName) FROM T_Person

展示效果的SQL：

MySQL:

SELECT FName, RTRIM('   123abc   '), LENGTH(RTRIM('   123abc   '))

Oracle:

SELECT FName, RTRIM('   123abc   '), LENGTH(RTRIM('   123abc   ')) FROM DUAL

2.6 去除字符串两侧空格

SELECT FName, LTRIM('   123abc   '), LENGTH(LTRIM('   123abc   ')) FROM DUAL

展示效果的SQL：

MySQL:

SELECT '  123  abc  ', LENGTH('  123  abc  '), LENGTH(TRIM('  123  abc  '))

Oracle:

SELECT '  123  abc  ', LENGTH('  123  abc  '), LENGTH(TRIM('  123  abc  ')) FROM DUAL

 

 

2.7 取子字符串

SQL中提供用来计算子字符串的函数SUBSTRING(),其格式如下：

SUBSTRING(string, string_position, length)

 

不带表名的用法：

MySQL：

SELECT SUBSTRING('abcdef111', 2, 3)

Oracle:

SELECT SUBSTR('abcdef111',2,3) FROM DUAL

其中参数string为主字符串，start_position为子字符串在主字符串中的其实位置，length为子字符串的最大长度。在MySQL中支持这个函数，而在Oracle中这个函数中为SUBSTR()。其实这仅仅是名称不同而已，在用法没有什么不同。

带表名的用法：

MySQL:

SELECT FName, SUBSTRING(FName, 2, 3) FROM T_Person

Oracle:

SELECT FName, SUBSTR(FName, 2, 3) FROM T_Person

 

2.8 计算子字符串的位置

SQL中提供了计算子字符在朱子夫中位置的函数，这个函数可以检测指定子字符串是否存在于主字符串中，如果存在则可以返回所在位置。这个函数中MySQL和Oracle中为INSTR()，其参数格式如下：

INSTR(string, substring)

其中string为主字符串，参数substring为待查询的子字符串。如果string中存在substring子字符串，则返回子字符串第一个字符在主字符串出现的位置。如果string中不存在substring，则返回0.

MySQL、Oracle：

SELECT FName, INSTR(FName, 'm'), INSTR(FName, 'ly') FROM T_Person

 

2.9 从左侧开始去子字符串

使用SUBSTRING()汗水可以从任意位置开始去任意长度的子字符串，不过有的时候我们只需要要从左侧开始去子字符串，这样指定主字符串和要取的长度就可以了。但是如果使用SUBSTRING()函数的话仍然需要指定3个参数，其中第二参数为常量1。MySQL中提供LEFT()函数，从左侧开始去任意长度的子字符串，其参数格式如下：

LEFT(string, length)

在Oracle中不支持LEFT()函数，只能使用SUBSTR()函数来实现，也就是SUBSTR(string, 1, length)

MySQL:

SELECT FName, LEFT(FName, 3), LEFT(FName, 2) FROM T_Person

Oracle:

SELECT FName, SUBSTR(FName, 1, 3), SUBSTR(FName, 1, 2) FROM  T_Person

 

2.10 从右侧开始去子字符串

使用SUBSTRING()函数可以从任意位置开始取任意长度字符串，不过有是有的时候我们只需要从右侧开始去子字符串，这样指定主字符串和要取的长度就可以了。不过如果使用SUBSTRING()仍然需要指定3个参数。MySQL中提供RIGHT函数，用于从右侧开始去任意长度的子字符串。参数格式如下：

RIGHT(string, length)

其中参数string为主字符串，length为子字符串的最大长度。

Oracle中不支持RIGHT()函数，只能使用SUBSTR()函数来实现，其中起始位置如下表达式计算出来：

startposition = LENGTH(string) - length+1

也就是SUBSTR(string, LENGTH(string)-length+1, length)等价于RIGHT(string, length)

执行下面的SQL语句。

MySQL：

SELECT FName, RIGHT(FName, 3), RIGHT(FName, 2) FROM T_Person

Oracle：

SELECT FName, SUBSTR(FName, LENGTH(FName)-3+1, 3), SUBSTR(FName, LENGTH(FName)-2+1, 2) FROM T_Person

 

2.11 字符串替换

REPLACE()函数可以用来将指定的子字符串替换为其他的字符串，比如将“Hello world”中的“r1”替换为“ok”后得到“Hello World”而把“Just so so”中的“s”替换为“z”后得到“Just zo zo”。REPLACE()函数的参数格式如下：

REPLACE(string, string_tobe_replace, string_to_replace)

其中参数string为要进行替换操作的主字符串，参数string_tobe_replace为要被替换的字符串，而string_to_replace将替换string_tobe_replace中所有出现的地方。

执行下面的SQL语句：

SELECT FName, REPLACE(FName, 'i', 'e'), FIDNumber,REPLACE(FIDNumber, '2345', 'abcd') FROM T_Employee

SQL中没有提供删除字符串中匹配的子字符串的方法，因为使用REPLACE()函数就可以删除字符串。那就是将第三个参数设定为空字符串，用空字符串来替换字符串，这样也就达到删除指定子字符串的效果了。比如下面的SQL语句用来将FName中的m及FIDNumber中的123删除：

SELECT FName, REPLACE(FName, 'm', ''), FIDNumber, REPLACE(FIDNumber, '123', '') FROM T_Person

补充：

LTRIM()、RTRIM()和TRIM()都只能删除两侧的字符串，无法删除字符串中间的空格，而使用REPLACE()函数可以完成这个功能，也就是用空字符串替换所有的空格。执行下面的SQL语句。

MySQL:

SELECT REPLACE('   abc 123 wpf',' ',''), REPLACE('   ccw enet wcf f')

Oracle:

SELECT REPLACE('   abc 123 wpf','',''), REPLACE('   ccw enet wcf f') FROM DUAL

 

2.12得到字符的ASCII码

ASCII()函数用来得到一个字符的ASCII码，它有且只有一个参数，这个参数为待求ASCII码的字符，如果参数为一个字符串则函数返回第一个字符的ASCII码，比如执行下面的SQL语句。

MySQL：

SELECT  ASCII('a'), ASCII('abc')

Oracle:

SELECT ASCII('a'), ASCII('abc') FROM DUAL

 

下面的SQL语句用来计算每个员工姓名的第一个字符的ASCII码。

MySQL:

SELECT FName, LEFT(FName, 1), ASCII(LEFT(FName, 1)), ASCII(FName) FROM T_Person

Oracle:

SELECT FName, SUBSTR(FName, 1, 1), ASCII(SUBSTR(FName, 1, 1)), ASCII(FName) FROM T_Person

 

2.13 得到一个ASCII码数字对应的字符

与ASCII()函数正好相反，SQL还提供了用来得到一个字符的ASCII码的函数。中MySQL，这个函数是CHAR()，而在Oracle中这个函数则为CHR()。

MySQL:

SELECT CHAR(56), CHAR(90), 'a', CHAR(ASCII('a'))

Oracle:

SELECT CHR(56), CHR(90),'a', CHR(ASCII('a')) FROM DUAL

 

2.14 发音匹配度

到目前为止，所有关于字符串的匹配都是这对其乒协形式的。但有时候我们并不知道一个人姓名准确的拼音。中公安、医疗、教育等系统中经常需要根据发音来检索姓名，比如“检索名字发音为和[jeck]类似的人员”，这时我们就要进行发音的匹配度测试了。

SQL中提供了SOUNDEX()函数，用于计算一个字符串的发音特征值，这个特征值为一个4个字符的字符串，特征值的第一个字符串总是初始字符串中的第一个字符，而其后则是一个2位数字的数值。下面的SQL语句用于查询几个名字的发音特征值。

MySQL:

SELECT SOUNDEX('jack'), SOUNDEX('jeck'), SOUNDEX('joke'), SOUNDEX('juke'), SOUNDEX('look'), SOUNDEX('jobe')

Oracle:

SELECT SOUNDEX('jack'), SOUNDEX('jeck'), SOUNDEX('joke'), SOUNDEX('juke'), SOUNDEX('look'), SOUNDEX('jobe') FROM DUAL

下面的SQL语句用于查询公司所有员工姓名的发音特征值：

SELECT FName, SOUNDEX(FName) FROM T_Person

下面的SQL语句用来计算每个人的姓名发音与“Merry”相似度：

SELECT FName, DIFFERENCE(FName, 'Merry') FROM T_Person

 

在WHERE语句中使用difference()更有意义，比如下面的SQL语句用于查询和“Tim”发音相似度大于3的员工：

SELECT * FROM T_Person WHERE DIFFERENCE(FName, 'Tim') >= 3