《SQL经典实例》第六章 处理字符串

        在大多数情况下，你希望以每次穿过一个字符的方式来遍历字符串，可惜在 SQL 中，这种任务完成起来并不容易。由于 SQL 的循环功能有限，你需要通过模拟循环来遍历字符串。我们将这种操作称为“走查字符串”，本章的第一个实例对此做了介绍。使用 SQL 来分析字符串时，这种操作非常重要，本章的大部分实例参照并使用了它。强烈建议你弄清楚这种方法的工作原理。

--6.1 走查字符串
     select substring(e.ename,iter.pos,1) as C
     from (select ename from emp where ename = 'KING') e,
         (select id as pos from t10) iter
     where iter.pos <= datalength(e.ename)

    内嵌视图 E 的基数为 1，而内嵌视图 ITER 的基数为 10，因此笛卡儿积为 10 行。要在 SQL 中模拟循环，首先需要生成这样的积。
    
--6.2 在字符串字面量中嵌入引号
    select 'g''day mate' qmarks from t1
    输出：g'day mate
    SQL中的单引号即：''  （两个单引号） 单引号总是成对出现 

--6.3 计算字符串中特定字符的出现次数
    删除字符串中的逗号，再将原来的字符串长度与删除逗号后的字符串长度相减，就可以确定字符串中包含多少个逗号
    select (datalength('10,CLARK,MAN,AGER')-
       datalength(replace('10,CLARK,MANAGER',',','')))/datalength(',')
       as cnt
       from t1
    第二行的除法，仅当要查找的字符串的长度大于 1 时，这个除法运算才是必不可少

--6.4 将不想要的字符从字符串中删除
    SQL Server 2017中才新增了translate内置函数，低版本中运行会报错：'translate' 不是可以识别的 内置函数名称。
     select ename,
        replace(translate(ename,'aaaaa','AEIOU'),'a','') as stripped1,
        sal,
        replace(cast(sal as char(4)),'0','') as stripped2
     from emp

--6.5 将数字数据和字符数据分开
    SELECT REPLACE
	( translate ( DATA, '0123456789', '0000000000' ), '0', '' ) AS ename,
	cast(
		REPLACE ( translate ( lower( DATA ), 'abcdefghijklmnopqrstuvwxyz', replicate ( 'z', 26 ), 'z', '' ) AS INTEGER ) AS sal 
FROM
	( SELECT concat( ename, sal ) AS DATA FROM emp ) x

--6.6 判断字符串是否只包含字母和数字
    查找所有的字母数字字符。采取这种方法时，可以将所有的字母数字字符都转换为特定字符，从而将它们视为单个字符进行处理。为什么要这样做呢？这是因为这样可以将字母数字字符作为一个整体进行操作。将所有的字母数字字符都转换为你选择的字符后，将字母数字字符与其他字符隔离就易如反掌了。
    SELECT DATA 
FROM
	V 
WHERE
	translate ( lower( DATA ), '0123456789abcdefghijklmnopqrstuvwxyz', replicate ( 'a', 36 ) ) = replicate ( 'a', len ( DATA ) )
    replicate(char,length):SQL SERVER 内置函数，用char填充指定长度的字符串，并返回

--6.7 提取姓名中的首字母
    解决思路：因为不如编程语言灵活，所以对字符串的格式要求比较严格。处理方法为先将姓名中的小写字母翻译为#，然后将#号去除，再将姓名中原有的空格替换成‘.’。
     select replace(
        replace(
        translate(replace('Stewie Griffin', '.', ''),
                  'abcdefghijklmnopqrstuvwxyz',
                  replicate('#',26) ), '#','' ),' ','.' ) + '.'
   from t1

--6.8 根据部分字符串排序
    select ename
    from emp
    order by substring(ename,len(ename)-1,2)

--6.9 根据字符串中的数字排序
    解决思路：先将字符串中的空格和字母替换成统一字符#  然后将将#号删除，再进行排序。使用DBMS的内置函数translate和replace函数实现。
    SELECT DATA 
FROM
	V 
ORDER BY
	cast(
		REPLACE (
			translate (
				DATA,
			REPEAT
					( '#', length( DATA ) ),
					REPLACE ( translate ( DATA, '##########', '0123456789' ), '#', '' ) 
				),
				'#',
				'' 
			) AS INTEGER 
	)
--6.10 根据表中的行创建分隔列表
    解决思路：SQL2017新增内置函数STRING_AGG（），进行字符串的分组和拼接。
    SELECT
    city, 
    STRING_AGG(email,';') email_list
FROM
    sales.customers
GROUP BY
    city;

--6.11 将分隔数据转换为多值in列表
    SQL Server内置函数：Substring（data,startIndex,datalength） 截取字符串data 的从startIndex开始的datalength个字符。
    SQL Server内置函数：charindex(char,data)，查找字符串data中char所在得序号。
    解决方法：
    SELECT empno,
       ename,
       sal,
       deptno
FROM   emp
WHERE  empno IN (SELECT Substring(c, 2, Charindex(',', c, 2) - 2) AS empno
                 FROM   (SELECT Substring(csv.emps, iter.pos, Len(csv.emps)) AS c
                         FROM   (SELECT ',' + '7654,7698,7782,7788' + ',' AS emps
                                 FROM   t1) csv,
                                (SELECT id AS pos
                                 FROM   t100) iter
                         WHERE  iter.pos <= Len(csv.emps)) x
                 WHERE  Len(c) > 1
                        AND Substring(c, 1, 1) = ',') 
    上面SQL得解决思路：通过将字符串放在两个逗号（分隔符）内，可以避免执行特殊的检查来确定字符串的起始位置或终止位置。下一步是只保留要在 IN 列表中使用的值。需要保留的是那些以逗号开头的行，但仅包含逗号的最后一行除外。使用函数 SUBSTR 或 SUBSTRING 找出以逗号开头的行，然后保留这些行中从开头的逗号到下一个逗号之间的所有字符。完成这项工作后，将得到的字符串转换为数字，以便与数值列 EMPNO（第 4~14 行）进行比较。

--6.12 按字母顺序排列字符串中得字符
    解决方法：SQL Server 2017及以上版本使用string_agg（）  
    select ename,STRING_AGG(c,'') rename
FROM   (SELECT e.ename,
               Substring(e.ename, iter.pos, 1)AS c,
               Row_number() OVER (partition BY e.ename ORDER BY Substring(e.ename, iter.pos, 1)) AS pos
        FROM   emp e,
               (SELECT Row_number()OVER(ORDER BY ename) AS pos
                FROM   emp) iter
        WHERE  iter.pos <= Len(e.ename)) x