length : 表示字符串长度
replace:单体替换
replace(str1,str2,str3)
说明:str3替换str1中出现的所有str2,返回新的字符串,如果有某个参数为NULL,此函数返回NULL
该函数可以多次替换,只要str1中还有str2存在,最后都被替换成str3
若str3为空,则删除str2
translate:批量替换
TRANSLATE ( 输入字符串, 要替换的字符, 替换成的字符)
repeat:
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
substr:
substr(str,pos,len): 从pos开始的位置,截取len个字符
substr(str,pos): pos开始的位置,一直截取到最后
第六章 使用字符串
- 遍历字符串
- 解决方案:使用笛卡尔积来生成行号,用来在该返回字符串中的每个字符。然后,使用DBMS中的内置的字符串分析函数来摘出所要显示的字符(SQL server用户可使用substring代替substr)
- 字符串文字中包含引号
- 使用引号(‘’)时,可以将它们当做括号()看待,引号必须成对
- 计算字符在字符串中出现的次数
- 解决思路:先计算出原字符串的长度,然后计算去掉逗号后字符串的长度,差就是逗号在该字符串中出现的次数。每种DBMS都提供了求字符串的长度以及删除某个字符的函数。多数为(length和replace(SQL server是len函数,而不是length))
- 最后一步的操作是将计算出的差值除以要查找的字符串长度。如果所要查找的字符串的长度大于1时,这一步不能少。
- select (length(‘字符串’)-length(replace(‘字符串’),‘目标字符’,‘ ’))/length(‘目标字符’)
- 从字符串中删除不需要的字符
- 解决思路:每种DBMS都提供了用来从字符串中删除不需要的字符,常用的是replace和translate
- DB2
- 使用内置idtranslate和replace
- MySQL和SQL server
- 不支持translate,只能多次调用replace
- Oracle和PostgreSQL
- 使用内置idtranslate和replace
- 将字符串和数字数据分离
- 解决思路:使用内置函数translate和replace,将多个字符转换为单个字符。用一个字符来代替所有的数字或者一个字符代表所有字符。但是首先需要用translate将所有的字符数据隔开!
- DB2
- 使用内置idtranslate和replace
- Oracle
- 使用内置idtranslate和replace
- PostgreSQL
- 使用内置idtranslate和replace
- 判别字符串是不是字母数字型
- 解决思路:查出所有的字母与数字字符。将所有的字母与数字用所选定的字符替代后,就可以轻松的将字母数字与其他字符分开。
- DB2
- 使用translate将所有字母与数字转为同一个字符,然后判断哪些行中还有没被转换的字符。
- MySQL
- 使用正则
- Oracle和PostgreSQL
- 使用translate将所有字母与数字转为同一个字符,然后判断哪些行中还有没被转换的字符。
- SQL server
- 不支持translate,因此必须判断每一行中只包含非数字字母。
- 提取姓名的大写首字母缩写(假定名字都是由名和姓,或者由名,中间名(中间名缩写),及姓组成)
- DB2
- 使用内置函数replace,translate和repeat来提取大写首字母缩写
- MySQL
- 使用内置concat,concat_ws,substring和substring_index来提取出大写首字母缩写。
- Oracle和PostgreSQL
- 使用内置函数replace,translate和rpad来提取出大写首字符缩写
- SQL server
- 不支持translate及concat_ws等函数
- DB2
- 按字符串中的部分内容排序
- 解决思路:找到并使用相应的DBMS 的内置函数来提取出要据以排序的子字符串,代表性的函数就是substr函数
- DB2, Oracle,MySQL和PostgreSQL
- 联合使用内置函数length和substr来按照字符串指定的部分进行排序
- SQL server
- 使用substring和len来按照字符串的指定部分进行排序
- 按字符串中的数值排序
- 解决思路:不同的DBMS中的解决方案使用的函数与语法都不尽相同,但是方法(使用内置的函数replace和translate)是相同的。具体就是使用replace和translate来从字符串中删除非数字字符,只留下用于排序的数字值。
- DB2
- 使用内置函数replace和translate来根据字符串中的数字字符排序
- Oracle
- 使用内置函数replace和translate来根据字符串中的数字字符排序
- PostgreSQL
- 使用内置函数replace和translate来根据字符串中的数字字符排序
- MySQL和SQL server
- 不支持translate函数
- 根据表中的行创建一个分隔列表
- 解决思路:找到对应DBMS中的内置函数,来解决
- DB2
- 使用递归的with函数来构建分界列表
- MySQL
- 使用内置函数group_concat来构建分隔列表
- Oracle
- 使用内置函数sys_connect_by_path来构建分隔列表
- PostgreSQL
- 不提供用来创建分隔列表的函数,所以必须预先知道在这个列表中将有多少值。知道对打列表的大小,就可以判断需要添加的项数,从而用标准的变换和连接操作来创建分隔列表。
- SQL server
- 使用递归的with函数来构建分隔列表
- 将分隔数据转换为多值IN列表
- 解决思路:将字符串分解为各个单独的empno。遍历字符串,但并不是一个字符一个字符的遍历,而是将这个字符串转换成有效的empno值。
- DB2
- 通过遍历传递给in列表的字符串,使用函数row_nomber,locate和substr
- MySQL
- 通过遍历传递给in列别的字符串,可以轻松的将其转换为若干行
- Oracle
- 通过遍历传递给in列别的字符串,使用rownum,substr和instr
- PostgreSQL
- 通过遍历传递给in列别的字符串,使用函数row_nomber,charindex和substring
- 按字母顺序排序字符串
- DB2
- 遍历每个字符串,然后对其中的字符进行排序
- MySQL
- 使用group_concat函数,该函数不仅将构成的字符连接起来,还对它们进行排序
- Oracle
- 使用函数sys_connect_by_path允许以迭代方式构建一张列表
- PostgreSQL
- 不支持内置函数,所以在遍历钱哥字符串之外,还需要预先知道姓名的最大长度,在这种解决方案中,使用视图v来增加它的可读性
- SQL server
- 按照字符顺序排列各行字符串中的字符,需要遍历每个字符串,然后将这些字符排序
- DB2
- 判别可作为数值的字符串
- 解决思路:使用函数replace和translate对于操作字符串和单独的字符非常有用。关键就是先将所有的数字准换成同一个字符,这样就可以很轻易的通过一个字符将数字隔离并识别出来,然后将去除了数字的剩余字符和原本字符作比较,存在的转换为#,转化完成后,删除#,剩下的就是数字了。
- DB2
- 使用translate,replace,posstr函数来隔离每一行汇总的数字字符。必须调用cast,使用replace来删除多余的空白
- MySQL
- 因为没有函数translate,所以必须遍历每一行,并且给予一个字符地计算
- Oracle
- 使用translate,replace,posstr函数来隔离每一行汇总的数字字符。不必调用cast,使用replace函数来删除多余的空白。
- PostgreSQL
-
- 使用translate,replace,posstr函数来隔离每一行汇总的数字字符。不必调用cast,使用replace函数来删除多余的空白
-
- SQL server
- 使用内置函数isnumeric配合使用通配符搜索可以轻易的识别包含数字的字符串。但是将数字从字符串中提取出来不易,因为不支持translate
- 提取第n个分隔的子串
- 解决思路:将每个子串作为单独的行返回,并保留每个子串在列表中的顺序。
- DB2
- 使用函数row_number只保留每个字符串中的第n个子串
- MySQL
- 根据逗号的位置,来只返回每个字符串中的第n个子串
- Oracle
- 使用substr和instr函数来找到每个列表中的第n个子串
- PostgreSQL
- 使用split_part函数将每个单独的子串作为一行返回
- SQL server
- 使用函数row_number来保留每个字符串中的第n个姓名
- 分解ip地址
- 解决思路:使用内置函数找到点的位置及前后的数字
- DB2和SQL server
- 使用递归的with字句来模拟对ip地址的反复处理,使用usbstr函数将其分解。在ip地址前加一个句点,使得每组数字前面都有句点,或在后面添加。
- MySQL
- 使用substr_index函数可以轻松的分解ip地址
- Oracle
- 使用内置的substr和instr来分解ip地址
- POstgreSQL
- 使用内置函数split_part来分解ip地址