读书笔记--操作字符串

1、计算特定字符串在某字符串中出现的次数

解决方案：首先计算出原字符的长度，然后计算出相应字符的长度， 两者相减即为所计算字符的个数。

例：计算逗号在字符串中出现的次数

 select col,length(col)-length(replace(col,',','')) as comma_zs  from tab1

COL        COMMA_ZS  
---------- -----------
aaa,bb,c             2
ab,dc,bb,c           3

  2 record(s) selected.

如果输入字符串为多个字符，需要在得出的结果/length(字符串)

2、删除字符串中特定的字符

解决方案：使用replace与trancelate函数来达到相应的目的，通常找一特殊字符，介个字符最好不要在列中出现，否则后果自负啊。。。

[db2inst1@db2qas01 ~]$ db2 "select * from tab2"

ENAME                SAL       
-------------------- -----------
SMITH                        800
ALLEN                       1600
WARD                        1250
JONES                       2975
MARTIN                      1250
BLAKE                       2850
CLARK                       2450
SCOTT                       3000

  8 record(s) selected.

现在准备将第一列 ENAME中的所有元音字母删除掉，将SAL列中所有的0去掉:

select ename,replace(translate(ename,'AEIOU','@@@@@'),'@','')  ename_1,sal,replace( cast(sal as varchar(20)),'0','')  as sal2 from tab2

ENAME                ENAME_1              SAL         SAL2               
-------------------- -------------------- ----------- --------------------
SMITH                SMTH                800            8                  
ALLEN                LLN                   1600         16                 
WARD                 WRD                  1250        125                
JONES                JNS                      2975        2975               
MARTIN               MRTN               1250       125             
BLAKE                BLK                    2850       285            
CLARK                CLRK                  2450      245                
SCOTT                SCTT                    3000     3  

ENAME                ENAME_1              SAL         SAL2               
-------------------- -------------------- ----------- --------------------
SMITH                SMTH                         800 8                  
ALLEN                LLN                         1600 16                 
WARD                 WRD                         1250 125                
JONES                JNS                         2975 2975               
MARTIN               MRTN                        1250 125                
BLAKE                BLK                         2850 285                
CLARK                CLRK                        2450 245                
SCOTT                SCTT                        3000 3