SQL字符和数字相关函数

===============================================================================

字符（字符串）操作汇总===============================================================================

1、CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast

SELECT CONCAT(CAST(int_col AS CHAR), char_col)

mysql> SELECT CONCAT(’My’, ‘S’, ‘QL’);

-> ‘MySQL‘

mysql> SELECT CONCAT(’My’, NULL, ‘QL’);

-> NULL

mysql> SELECT CONCAT(14.3);

-> ‘14.3′

2、CONCAT_WS(separator,str1,str2,…)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

mysql> SELECT CONCAT_WS(’,',’First name’,'Second name’,'Last Name’);

-> ‘First name,Second name,Last Name’

mysql> SELECT CONCAT_WS(’,',’First name’,NULL,’Last Name’);

-> ‘First name,Last Name’

CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

3、concat和concat_ws()区别

3.1、concat()函数

MySQL的concat函数可以连接一个或者多个字符串,如

mysql> select concat('10');

>10 |

mysql> select concat('11','22','33');

>112233 |

而Oracle的concat函数只能连接两个字符串

SQL> select concat('11','22') from dual;

MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

mysql> select concat('11','22',null);

> NULL |

而Oracle的concat函数连接的时候，只要有一个字符串不是NULL,就不会返回NULL

SQL> select concat('11',NULL) from dual;

> 11

3.2、concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接

如连接后以逗号分隔

mysql> select concat_ws(',','11','22','33');

>11,22,33

和concat不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL

mysql> select concat_ws(',','11','22',NULL);

>11,22 |

4、group_concat()可用来行转列, Oracle没有这样的函数

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

如下例子

mysql> select * from aa;

+------+------+

| id | name |

+------+------+

| 1 | 10 |

| 1 | 20 |

| 2 | 20 |

| 3 | 200 |

| 3 | 500 |

+------+------+

6 rows in set (0.00 sec)

4.1、以id分组，把name字段的值打印在一行，逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;

+------+--------------------+

| id | group_concat(name) |

+------+--------------------+

| 1 | 10,20,20 |

| 2 | 20 |

| 3 | 200,500 |

+------+--------------------+

3 rows in set (0.00 sec)

4.2、以id分组，把name字段的值打印在一行，分号（；）分隔

mysql> select id,group_concat(name separator ';') from aa group by id;

+------+----------------------------------+

| id | group_concat(name separator ';') |

+------+----------------------------------+

| 1 | 10;20;20 |

| 2 | 20 |

| 3 | 200;500 |

+------+----------------------------------+

3 rows in set (0.00 sec)

4.3、以id分组，把去冗余的name字段的值打印在一行，逗号分隔

mysql> select id,group_concat(distinct name) from aa group by id;

+------+-----------------------------+

| id | group_concat(distinct name) |

+------+-----------------------------+

| 1 | 10,20 |

| 2 | 20 |

| 3 | 200,500 |

+------+-----------------------------+

3 rows in set (0.00 sec)

4.4、以id分组，把name字段的值打印在一行，逗号分隔, name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;

+------+---------------------------------------+

| id | group_concat(name order by name desc) |

+------+---------------------------------------+

| 1 | 20,20,10 |

| 2 | 20 |

| 3 | 500,200 |

+------+---------------------------------------+

3 rows in set (0.00 sec)

5、repeat()函数，用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数

mysql> select repeat('ab',2);

>abab

mysql> select repeat('a',2);

>aa |

6**、stuff()函数

将字符串中从某个字符开始截取一段字符，然后将另外一个字符串插入此处

select stuff('hello,world!',4,4,'****') --返回值hel****orld!

7、substring()函数

返回从指定位置开始指定长度的字符串

select substring('Hello,World!',2,10) --返回值ello,World

8、replace()函数

将字符串中某段字符替换为指定的字符串

select replace('hello,world!','ll','aa') --返回值heaao,world!

9、ltrim()函数

去除字符串中左边的空格

select ltrim(' hello,world!') --返回值hello,world!

10、rtrim()函数

去除字符串中左边的空格

select rtrim('hello,world! ') --返回值hello,world!

11、trim()函数

去除字符串中左边和右边的空格

select trim(' hello,world! ') --返回值hello,world!

12、isnull()函数

将NULL值替换为指定字符

select isnull('a',null) --返回值a

13、转换数据类型cast()，convert()

select cast('2007-10-11' as datetime) --返回值2007-10-11 00:00:00.000

**select convert(datetime,'2007-10-11') --返回值2007-10-11 00:00:00.000

14、获取字符串长度length()

select length('hello,world!') --返回值12

15、获取字符串的前3个字符left()

select left('hello,world!',3) --返回值hel

16、获取字符串的后3个字符right()

select right('hello,world!',3) --返回值ld!

17、去除字符串的前3个字符

select right('hello,world!',(length('hello,world!')-3)) --返回值lo,world!

18、去除字符串的后3个字符

select left('hello,world!',(length('hello,world!')-3)) --返回值hello,wor

19、获取在该字符串中某字符串的位置(返回数字)

select charindex('e','hello,world!') --返回值2

20、返回从第2个字符开始前4个字符

select left(right('[bbbb]aaa',len('[bbbb]aaa')-1),4) --返回值 bbbb

{对汉语支持不是很好，英语是正确的}

21、返回字符的小写形式lower()

select lower('HELLO,WORLD!') --返回值hello,world!

22、返回字符的大写形式upper()

select UPPER('hello,world!') --返回值HELLO,WORLD!

23、用第三个表达式替换第一个字符串表达式中出现的所有第二个指定字符串表达式的匹配项（如果其中有一个输入参数属于 nvarchar 数据类型，则返回 nvarchar；否则返回 varchar。如果任何一个参数为 NULL，则返回 NULL。）

SELECT REPLACE('Hello,World!','l','a') --返回值Heaao,Worad!

SELECT REPLACE('Hello,World!','l','') --返回值Heo,Word!

SELECT REPLACE('Hello,World!','l',null) --返回值NULL

24**、以右边参数数值次数复制字符表达式

select REPLICATE('Hello,World!',4)

--返回值Hello,World!Hello,World!Hello,World!Hello,World!

25、返回反转后的字符串

select REVERSE('Hello,World!') --返回值!dlroW,olleH

26**、使用DIFFERENCE时，两个字符串发音越相似（仅限于英文字符），返回值越大（返回值在0-4之间）

DIFFERENCE('sun','san') --返回值4

DIFFERENCE('sun','safdsdf') --返回值3

DIFFERENCE('sun','dgffgfdg') --返回值0

27**、将带小数点的数字类型转换为可设定长度可设定小数位的四舍五入后的字符串

SELECT STR(123.34584, 7, 3) --返回值123.346

28**、当设定长度值小于整数部位长度时，字符串将返回设定长度个*

SELECT STR(123333.34584, 5, 4) --返回值*****

29、POSITION(substr IN str)

返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.

mysql> select LOCATE('bar', 'foobarbar');

-> 4

mysql> select LOCATE('xbar', 'foobar');

-> 0

该函数是多字节可靠的。 LOCATE(substr,str,pos)

返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0。

mysql> select LOCATE('bar', 'foobarbar',5);

-> 7

这函数是多字节可靠的。

INSTR(str,substr)

返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同，除了参数被颠倒。

mysql> select INSTR('foobarbar', 'bar');

-> 4

mysql> select INSTR('xbar', 'foobar');

-> 0

这函数是多字节可靠的。

30、LPAD(str,len,padstr)

返回字符串str，左面用字符串padstr填补直到str是len个字符长。

mysql> select LPAD('hi',4,'??');

-> '??hi'

RPAD(str,len,padstr)

返回字符串str，右面用字符串padstr填补直到str是len个字符长。

mysql> select RPAD('hi',5,'?');

-> 'hi???'

31、字符串的截取：

MID(str,pos,len)

SUBSTRING(str,pos,len)

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING_INDEX(str,delim,count)

MID(str,pos,len) SUBSTRING(str,pos,len)从字符串str返回一个len个字符的子串，从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。

mysql> select SUBSTRING('Quadratically',5,6);

-> 'ratica'

该函数是多字节可靠的。

SUBSTRING(str,pos)SUBSTRING(str FROM pos)从字符串str的起始位置pos返回一个子串。

mysql> select SUBSTRING('Quadratically',5);

-> 'ratically'

mysql> select SUBSTRING('foobarbar' FROM 4);

-> 'barbar'

该函数是多字节可靠的。

SUBSTRING_INDEX(str,delim,count)返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)。

mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);

-> 'www.mysql'

mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);

-> 'mysql.com'

该函数对多字节是可靠的

32、按位(bit)来取字符，并组成字符串集合。

MAKE_SET(bits,str1,str2,...)

返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。在str1, str2, ...中的NULL串不添加到结果中。

mysql> SELECT MAKE_SET(1,'a','b','c');

-> 'a'

mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');

-> 'hello,world'

mysql> SELECT MAKE_SET(0,'a','b','c');

-> ''

33、按位(bit)来取on,off，得到一个确定位的字符串集合。

EXPORT_SET(bits,on,off,[separator,[number_of_bits]])

返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用。

mysql> select EXPORT_SET(5,'Y','N',',',4)

-> Y,N,Y,N

34、LOAD_FILE(file_name)

读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

mysql> UPDATE table_name

SET blob_column=LOAD_FILE("/tmp/picture")

WHERE id=1;

35、MySQL必要时自动变换数字为字符串，并且反过来也如此：

mysql> SELECT 1+"1";

-> 2

mysql> SELECT CONCAT(2,' test');

-> '2 test'

如果你想要明确地变换一个数字到一个字符串，把它作为参数传递到CONCAT()。

如果字符串函数提供一个二进制字符串作为参数，结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这只影响比较，不改变原值。

===============================================================================

数字操作汇总===============================================================================

1、返回指定数字的最大整数

select floor(123456.1234) --返回值123456

2、返回不带小数部分并且不小于其参数的值的最小数字。如果参数是一个空序列，则返回空序列

select ceiling(123.010) --返回124

select ceiling(null) --返回NULL

3、返回四舍五入后的最接近该数值的数值

select round(126.018,2) --返回126.12

4、返回一个0-1之间的FLoat类型的随机数

select rand() --返回0.94170703697981

5、返回圆周率PI的值

SELECT PI() --返回3.14159265358979

posted on 2013-09-27 17:41 Coyote.#2. 阅读(235) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

GoodGoodGood

SQL字符和数字相关函数

导航

公告