SQL字符和数字操作汇总.doc

SQL字符和数字相关函数=字符（字符串）操作汇总=1、CONCAT(str1,str2,) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 castSELECT 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 | | 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,World8、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) -返回值a13、转换数据类型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.00014、获取字符串长度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!) -返回值220、返回从第2个字符开始前4个字符select left(right(bbbbaaa,len(bbbbaaa)-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,olleH26*、使用DIFFERENCE时，两个字符串发音越相似（仅限于英文字符），返回值越大（返回值在0-4之间）DIFFERENCE(sun,san) -返回值4 DIFFERENCE(sun,safdsdf) -返回值3 DIFFERENCE(sun,dgffgfdg) -返回值0 27*、将带小数点的数字类型转换为可设定长度可设定小数位的四舍五入后的字符串SELECT STR(123.34584, 7, 3) -返回值123.34628*、当设定长度值小于整数部位长度时，字符串将返回设定长度个*SELECT STR(123333.34584, 5, 4) -返回值*29、POSITION(substr IN str) 返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0. mysql select LOCATE(bar, foobarbar); - 4mysql 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);- 4mysql select INSTR(xbar, foobar);- 0这函数是多字节可靠的。 30、LPAD(str,len,padstr) 返回字符串str，左面用字符串padstr填补直到str是len个字符长。 mysql select LPAD(hi,4,?);- ?hiRPAD(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);- raticallymysql select SUBSTRING(foobarbar FROM 4);- barbar该函数是多字节可靠的。 SUBSTRING_INDEX(str,delim,count)返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)。 mysql select SUBSTRING_INDEX(, ., 2);- www.mysqlmysql select SUBSTRING_INDEX(, ., -2);- 该函数对多字节是可靠的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