oracle字符串处理substr、dbms_lob.substr、case when

ゞ浴缸里的玫瑰 2021-09-25 09:12 415阅读 0赞

## 1. substr函数使用 ##

### 1.1 substr函数格式 (俗称：字符截取函数) ###

格式1： substr(string string, int a, int b);  
格式2：substr(string string, int a) ;

格式1：  
1、string 需要截取的字符串  
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）  
3、b 要截取的字符串的长度

格式2：  
1、string 需要截取的字符串  
2、a 可以理解为从第a个字符开始截取后面所有的字符串。

注：https://www.cnblogs.com/dshore123/p/7805050.html

### 1.2 获取字段长度： ###

length(string)计算string所占的字符长度：返回字符串的长度，单位是字符

select length('cctv') from dual

https://www.cnblogs.com/qingsong-do/archive/2012/09/28/2707024.html

### 1.3 检索字符在字符串中的位置 ###

instr  
https://blog.csdn.net/l\_degege/article/details/112664925

select a.fielddbtype,length(fielddbtype),instr(fielddbtype,'.'),
    substr(fielddbtype,instr(fielddbtype,'.')+1,length(fielddbtype))
     from workflow_billfield a 
     where a.id=50189

## 2. dbms\_lob.substr函数使用 ##

CLOB里存的是2进制  
判定长度 dbms\_lob.getlength(col1)  
获取文本 dbms\_lob.substr(col1,n,pos)  
dbms\_lob.substr(col1,10,1)表示从第1个字节开始取出10个字节

注：https://blog.csdn.net/daxiang12092205/article/details/20283359

-- dbms_lob.substr函数第二个参数有时不能为4000(3960或3000)，有时可以(测试数据4000个英文字符)
    select dbms_lob.substr(a.txt,3000,1),length(a.txt),DBMS_LOB.getlength(a.txt),a.* from jc_content_txt a
    -- 测试表，DBMS_LOB.SUBSTR为4000或5000均可
    select DBMS_LOB.SUBSTR(txt,5001) from jc_content_txt_test

以上问题可结合eclipse、jdbc、oracle做一个测试。

dbms\_lob.substr第二个参数是要从大型对象中抽取的字节数，这样可以理解测试sql中的4000或5000。

而第一条sql中第二个参数不能为4000(3960或3000)，估计是超过varchar2的4k限制（4k指字节还是字符），4k指的是字节。而DBMS\_LOB.SUBSTR第二个参数指的是字符的个数（通过测试可验证），它会转成varchar2。

dbms\_log.substr的第二个参数是字符数，非字节。

https://blog.csdn.net/swordmanwk/article/details/6204611

http://www.voidcn.com/article/p-tvbhgpdf-byk.html

https://www.thinbug.com/q/1797183

https://www.cnblogs.com/Tty725/p/13750663.html

https://blog.csdn.net/e\_wsq/article/details/7068091

https://www.cnblogs.com/grand-jon/p/7389427.html

https://blog.csdn.net/yfleng2002/article/details/7794784

https://www.cnblogs.com/zhixie/p/11968710.html

## 3.case when 使用 ##

CASE
       WHEN to_char(a.issuedt,'yyyy-mm-dd hh24:mi:ss') = '1900-01-01 00:00:00' THEN ''
       ELSE to_char(a.issuedt,'yyyy-mm-dd hh24:mi:ss')
    END issuedt ,

注：issuedt是日期类型，该值默认是原点时间。需求是如果是原点时间，则为空。

## 4. case when 结合getlength ##

SELECT *
    FROM
      (SELECT A.GUESTBOOK_ID AS ID,
              A.SITE_ID AS SITEID,
              A.CREATE_TIME AS CREATETIME,
              A.IS_CHECKED AS ISCHECKED,
              A.IS_RECOMMEND AS ISRECOMMEND,
              B.TITLE,
              case when DBMS_LOB.getlength(B.CONTENT) <=300 then dbms_lob.substr(B.CONTENT,300,1) else  CONCAT(dbms_lob.substr(B.CONTENT,300,1),'...') end AS CONTENT,
              dbms_lob.substr(B.REPLY) AS REPLY,
              B.NAME,
              A.GUESTBOOKCTG_ID AS CTGID,
              C.CTG_NAME AS CTGNAME
       FROM JC_GUESTBOOK A
       LEFT JOIN JC_GUESTBOOK_EXT B ON A.GUESTBOOK_ID = B.GUESTBOOK_ID
       LEFT JOIN JC_GUESTBOOK_CTG C ON A.GUESTBOOKCTG_ID = C.GUESTBOOKCTG_ID
       UNION ALL
     SELECT SERVICE_ID AS ID,
                        SITE_ID AS SITEID,
                        CREATE_TIME AS CREATETIME,
                        IS_CHECKED AS ISCHECKED,
                        IS_RECOMMEND,
                        SUBJECT AS TITLE,
                        case when DBMS_LOB.getlength(CONTENT) <=300 then dbms_lob.substr(CONTENT,300,1) else  CONCAT(dbms_lob.substr(CONTENT,300,1),'...') end AS CONTENT,
                        dbms_lob.substr(REPLAY) AS REPLY,
                        NAME,
                        99,
                        '企业服务'
       FROM JC_ENTERPRISESERVICE) T
    WHERE 1=1
      AND T.ISCHECKED=1
     ORDER BY T.CREATETIME DESC