SQL-cookbook学习笔记（六）~使用字符串

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开始的位置，一直截取到最后

                                            第六章 使用字符串

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