sql server 字符串中包含某字段个数 sql 包含字符串函数

转载

数据分析家 2023-08-18 22:50:46

文章标签 mysql sql 数据库字符串 bc 文章分类 SQL Server 数据库

文章目录

mysql字符串操作函数

1.计算字符串字符数的函数和字符串长度的函数
2.合并字符串函数
4.字母大小转换函数
7.删除空格的函数
9.重复生成字符串的函数REPEAT，空格函数SPACE(n)
10.替换字符串的函数
11.比较字符串大小的函数
12.截取子字符串的函数
13.匹配子串开始位置的函数
14.字符串逆序的函数
15.返回指定位置的字符串的函数
16.返回指定字符串位置的函数
17.返回子串位置的函数
18.选取字符串的函数

ASCII(str)
BIN(N)

mysql字符串操作函数

最近工作中需要接入对方数据库中的数据，所以很多地方用到了字符串函数，这里记录下来，以备后续学习。

函数名称	语法	功能说明
ASCII(str)	ASCII(str) int	返回字符串 str 中最左边字符的 ASCII 代码值
BIN(N)		返回十进制数值 N 的二进制数值的字符串表现形式
BIT_LENGTH()	BIT_LENGTH(str) int	返回字符串 str 所占的位长度
CHAR()		返回每一个传入的整数所对应的字符
CHAR_LENGTH()		单纯返回 str 的字符串长度
CHARACTER_LENGTH()		作用等同于CHAR_LENGTH()
CONCAT_WS()		返回串联并以某种分隔符进行分隔的字符串
CONCAT()		返回串联的字符串
CONV()		转换数值的进制
ELT()		返回一定索引处的字符串
EXPORT_SET()		返回一个字符串，其中，对于每个设置在bits中的位，得到一个on字符串，而对于每个未设定的位，则得到一个off字符串。
FIELD()		返回第一个参数在随后参数中的索引（下文中有时也称其为位置）
FIND_IN_SET()		返回第一个参数在第二个参数中的索引
FORMAT()		将数值参数进行一些格式化，并保留指定的小数位数
HEX()		返回参数的16进制数的字符串形式
INSERT()		在字符串的指定位置处，将指定数目的字符串替换为新字符串
INSTR()		返回子字符串第一次出现的索引
LCASE()		等同于 LOWER()
LEFT()		按指定规则，返回字符串中最左方的一定数目的字符
LENGTH()		返回字符串的字节长度
LOAD_FILE()		加载指定名称的文件
LOCATE()		返回子字符串第一次出现的位置
LOWER()		返回小写的参数
LPAD()		返回字符串参数，其左侧由指定字符串补齐指定数目
LTRIM()		去除前导空格
MAKE_SET()	MAKE_SET(bits,str1,str2,…)	返回一个由逗号分隔的字符串集，其中每个字符串都拥有bits 集中相对应的二进制位
MID()		从指定位置返回子字符串
OCT()		将参数转变成八进制数，返回这个八进制数的字符串形式
OCTET_LENGTH()		等同于 LENGTH()
ORD()		如果参数中最左方的字符是个多字节字符，则返回该字符的ASCII代码值
POSITION()		等同于 LOCATE()
QUOTE()		对参数进行转义，以便用于 SQL 语句
REGEXP		使用正则表达式进行模式匹配
REPEAT()		按指定次数重复字符串
REPLACE()		查找更换指定的字符串
REVERSE()		反转字符串参数中的字符
RIGHT()		返回字符串参数最右边指定位数的字符
RPAD()		将字符串按指定次数重复累加起来
RTRIM()		除去字符串参数的拖尾空格
SOUNDEX()		返回一个soundex字符串
SOUNDS LIKE		对比声音
SPACE()		返回指定空格数目的字符串
STRCMP()		对比两个字符串
SUBSTRING_INDEX()		将字符串参数中在指定序号的分隔符之前的子字符串予以返回
SUBSTRING() 与 SUBSTR()		按指定规则返回子字符串
TRIM()		清除字符串参数的前导及拖尾空格
UCASE()		等同于 UPPER()
UNHEX()		将16进制数的每一位都转变为ASCII字符
UPPER()		将参数全转变为大写

上面即mysql官网上面的的所有字符串函数，但是平时工作中只用到部分，这里就将平时用到的一些字符串函数总结学习并且后续再学习再记录。

1.计算字符串字符数的函数和字符串长度的函数

BIT_LENGTH(str) CHARACTER_LENGTH(str) CHAR_LENGTH(str) LENGTH(str)

SELECT length('aa'), length('张三'), length('zhe里');
      	-->2            6                6
SELECT bit_length('aa'), bit_length('张三'), bit_length('zhe里');
        -->  16,  				48,  			48
SELECT CHARACTER_LENGTH('aa'), CHARACTER_LENGTH('张三'), CHARACTER_LENGTH('zhe里');
		-->  2	                    2								4
SELECT char_length('aa'), char_length('张三'), char_length('zhe里');
		-->  2,                2,                    4

总结：

LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或者字母算一个字节
CHAR_LENGTH(str) :返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。
CHARACTER_LENGTH(str) 作用等同于CHAR_LENGTH()
BIT_LENGTH(str)返回字符串所占的位长度，一个汉字为三个字节，一个字节有8位(bit)，

2.合并字符串函数

CONCAT(str1,str2,…) CONCAT_WS(separator,str1,str2,…)

SELECT concat('a', 'b'),concat('a', 'b', NULL);
		-->ab				NULL
SELECT concat_ws(';', 'a', 'b'), concat_ws(';', 'a', 'b', NULL, 'c');
		-->a;b						a;b;c

总结：

CONCAT(s1,s2,…)

CONCAT(s1,s2,…)返回结果为连接参数产生的字符串,一个或多个参数。
有任何一个参数为NULL,则返回值为NULL.
如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

CONCAT_WS(separator,str1,str2,…)

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

4.字母大小转换函数

LOWER(str)，LCASE(str), UPPER(str), UCASE(str)

SELECT lower('aaB'),  -->aab
       lower(NULL),  -->NULL
       lcase('asDf'),  -->asdf
       upper('aaB'),  -->AAB
       upper(NULL),  -->NULL
       ucase('asDf');  -->ASDF

总结：

a.LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。
b.UPPER(str)或者UCASE(str)函数将字符串中所有字母字符转换成大写字母。

7.删除空格的函数

LTRIM(str) RTRIM(str) TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

SELECT char_length(LTRIM(' ab c ')),          -->5
       char_length(RTRIM(' ab c ')),          -->5
       char_length(trim(' ab c '))            -->4
SELECT trim('??' FROM '?ab c??'),             -->?ab c
       trim(BOTH '??' FROM '?ab c??'),        -->?ab c
       trim(LEADING '??' FROM '?ab c??'),     -->?ab c??
       trim(TRAILING '??' FROM '?ab c???')    -->?ab c?

总结：

.LTRIM(str)返回字符串str,字符串左侧空格字符被删除
RTRIM(str)返回字符串str,字符串右侧空格字符被删除
TRIM( str)返回字符串str,字符串左侧、右侧空格字符被删除
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 这里的函数为trim的复杂式，TRIM( str)默认去除的str字符串左右两侧的空格字符串，如果想要去除左右两侧的其它字符，这里可以使用trim(’??’ FROM ‘?ab c??’)类似的表达式，其中trim(’??’ FROM ‘?ab c??’)等同于trim(BOTH ‘??’ FROM ‘?ab c??’)，如果只想去除一侧的字符，使用LEADING（左侧）或者TRAILING（右侧）
注意：中间的匹配字符串不删除

9.重复生成字符串的函数REPEAT，空格函数SPACE(n)

REPEAT(str,count) SPACE(N)

SELECT repeat('abc', 2),   	-->abcabc
       repeat('abc', 0),   	-->(空字符串)
       repeat('abc', -1),   -->(空字符串)
       repeat(NULL, 2),   	-->NULL
       repeat('abc', NULL)   -->NULL
SELECT space(2),   -->(两个空格字符串)
       space(0),   -->(空字符串)
       space(-1)   -->(空字符串)

总结：

REPEATE(s,n)返回一个有重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0,则返回一个空字符串。若s或n为NULL,则返回NULL。
SPACE(N) 返回N个空格字符串，如果N<=0 ,则返回0个空格字符

10.替换字符串的函数

INSERT(str,pos,len,newstr) REPLACE(str,from_str,to_str) LPAD(str,len,padstr) RPAD(str,len,padstr)

SELECT insert('aabbcc', 1, 2, 'efv'),  -->efvbbcc
       insert('aabbcc', 1, 4, 'efv'),  -->efvcc
       insert('aabbcc', 1, 18, 'efv'),  -->efv
       insert('aabbcc', 10, 2, 'efv'),  -->aabbcc
       insert('aabbcc', NULL, 18, 'efv'),  -->null
       insert('aabbcc', 2, 18, NULL);  -->null
       
SELECT REPLACE('1abcAbc', 'bc', '?'),   	-->1a?A?

SELECT lpad('hello', 9, 'abc'),   -->abcahello
       lpad('hello', 3, 'abc'),   -->hel
       rpad('hello', 9, 'abc'),   -->helloabca
       rpad('hello', 3, 'abc')    -->hel

总结：该函数的意思，pos为原来字符串的开始位置，从1开始，len为原来的字符串替换长度，将从pos–>pos+len字符串替换为新的字符串

a.INSERT(s1,x,len,s2)返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。
b.如果x超过字符串长度，则返回值为原始字符串。
c.假如len的长度大于其他字符串的长度，则从位置x开始替换。替换s2的所有。
d.若任何一个参数为NULL，则返回值为NULL。
REPLACE(str,from_str,to_str)使用字符串to_str替代字符串str中所有的字符串from_str
str,from_str,to_str任意一个出现了null，那么返回null
LPAD(str,len,padstr) 返回字符串str，其左边由字符串padstr填补到len字符串长度,假如str的长度大于len，则返回值被缩短至len字符。
RPAD(str,len,padstr) 函数方法同上

11.比较字符串大小的函数

STRCMP(expr1,expr2)

SELECT strcmp('aba', 'abc'),  	-->-1
       strcmp('aba', 'aba'),  	-->0
       strcmp('abc', 'aba')  	-->1

总结：

STRCMP(s1,s2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1

12.截取子字符串的函数

LEFT(str,len) RIGHT(str,len) SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

SELECT left('asdfg', 1),  -->a
       left('asdfg', 0),  -->        (空字符串)
       left('asdfg', -1),  -->        (空字符串)
       left('asdfg 3),  -->asd
       left('asdfg', 12),  -->asdfg
       right('asdfg', 1),  -->g
       right('asdfg', 0),  -->        (空字符串)
       right('asdfg', -1),  -->        (空字符串)
       right('asdfg', 3),  -->dfg
       right('asdfg', 12)  -->asdfg
SELECT substr('abcde', 2),  	-->bcde				等同于  substr('abcde' FROM 2)
       substr('abcde', 0),  	-->(空字符串)
       substr('abcde', -1),  	-->e
       substr('abcde', -4),  	-->bcde
       substr('abcd', NULL)  	-->NULL
SELECT substr('abcde', 2, 2),  	-->bc				等同于 substr('abcde' FROM 2 FOR 2)
       substr('abcde', 2, 0),  	-->(空字符串)
       substr('abcde', 2, -1),  -->(空字符串)
       substr('abcde', 2, 9);  	-->bcde

总结：

SUBSTRING(str,pos,len)带有len参数的格式，从字符串str返回一个长度同len字符相同的子字符串。
起始于位置pos,也可能对pos使用一个负值，若为负值，则子字符串的位置起始于字符串结尾pos字符，即倒数第pos个字符，而不是字符串的开头位置。
LEFT(str,len)返回字符串str开始的最左边len个字符，如果长度为复数或者为0，则返回空字符串，如果长度长于str长度，返回str
RIGHT(str,len) 返回字符串str开始的最右边len个字符，同上

13.匹配子串开始位置的函数

LOCATE(substr,str), LOCATE(substr,str,pos) POSITION(substr IN str) INSTR(str,substr)

mysql> SELECT LOCATE('bar', 'foobarbar');   -> 4
mysql> SELECT LOCATE('xbar', 'foobar'); -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);	-> 7
mysql> SELECT INSTR('foobarbar', 'bar');		-> 4
mysql> SELECT INSTR('xbar', 'foobar'); 		-> 0

总结：

LOCATE(substr,str), LOCATE(substr,str,pos) POSITION(substr IN str) INSTR(str,substr)3个函数的作用相同，返回字符串str1在字符串str的开始位置
注意函数locate 和instr函数的substr和str的位置，两者为镜像，一者为前，一者为后

14.字符串逆序的函数

REVERSE(str)

SELECT reverse('abd'),	-->dba
       reverse(NULL)	-->null

总结：

REVERSE(str)将字符串str反转，返回的字符串的顺序和str字符串顺序相反。

15.返回指定位置的字符串的函数

ELT(N,str1,str2,str3,…)

mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');        -> 'Aa'
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');        -> 'Dd'
mysql> SELECT ELT(6, 'Aa', 'Bb', 'Cc', 'Dd');         -> null

总结：

ELT(N,str1,str2,str3,…)若N=1，则返回字符串str1，若N=2,则返回字符串str2，依次类推。若N小于1或大于参数的数目，则返回NULL。

16.返回指定字符串位置的函数

FIELD(str,str1,str2,str3,…)

mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');        -> 2
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');        -> 0

总结：

FIELD(str,str1,str2,str3,…)返回字符串str在列表str1,str2,str3,…中第一次出现的位置，在找不到str的情况下，返回值为0.如果str为NULL,则返回值为0，原因是NULL不能同任何值进行同等比较
该方法可以用在order by 后面可以按照特定的顺序进行排序

17.返回子串位置的函数

FIND_IN_SET(str,strlist)

SELECT FIND_IN_SET('b', 'a,b,c,d'),		-->2
       FIND_IN_SET('b', 'a_b_c_d');		-->0

总结：

FIND_IN_SET(str,strlist) 返回字符串str在字符串列表strlist中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。如果str不在strlist或strlist为空字符串，则返回值为0.如果任意一个参数为NULL,则返回值为NULL。

18.选取字符串的函数

MAKE_SET(bits,str1,str2,…)

SELECT MAKE_SET(15, 'da', 'bad', 'adc', 'adf', 'adg'),		-->da,bad,adc,adf
       MAKE_SET(1, 'da', 'bad', 'adc', 'adf'),		        -->da
       MAKE_SET(2, 'da', 'bad', 'adc', 'adf'),		        -->bad
       MAKE_SET(4, 'da', 'bad', 'adc', 'adf');		        -->adc
       MAKE_SET(15, 'da', 'bad', NULL, 'adf', 'adg')        -->da,bad,adf

总结：

MAKE_SET(bits,str1,str2,…)返回由bits的二进制数指定的相应位的字符串组成的字符串，str1对应比特1,s2对应比特01以此类推。str1,str2…中的NULL值不会添加到结果中.
具体解释：2的二进制为10，反转后为01，也就是0,1分别对应’da’, ‘bad’, ‘adc’, ‘adf’，那么对应的1的只有bad，

ASCII(str)

返回字符串 str 中最左边字符的 ASCII 代码值。如果该字符串为空字符串，则返回0。如果字符串为 NULL 则返回 NULL。因为ASCII码表能表示的字符为256个，所以ASCII() 返回值在0-255之间。示例如下：

mysql> SELECT ASCII('2');
        -> 50
mysql> SELECT ASCII(2);
        -> 50
mysql> SELECT ASCII('dx');
        -> 100
mysql> SELECT ASCII(NULL);
     	-> null

BIN(N)

返回十进制数值 N 的二进制数值的字符串表现形式。其中，N 是一 BIGINT 型数值。该函数等同于 CONV(N, 10, 2)。如果 N 为 NULL，则返回 NULL

mysql> SELECT BIN(12);
        -> '1100'
        SELECT BIN('12');
        -> '1100'
        SELECT BIN('aa');
        -> 00
        SELECT bin(NULL);
        -> null

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。