SQL语句做字符串分割

SQL语句做字符串分割

在平时的工作中经常碰到字符串分割的业务，如“广东,广西,海南,贵州,云南”按照逗号进行分割：

[sql]

--用正则表达式

with t as (select '广东,广西,海南,贵州,云南' str from dual)

SELECT REGEXP_SUBSTR(str,'[^,]+',1,ROWNUM) province

FROM t

CONNECT BY ROWNUM<=LENGTH(str)-LENGTH(REPLACE(str,','))+1;

--不使用正则

with t as (select '广东,广西,海南,贵州,云南' str from dual)

SELECT substr(str,instr(','||str, ',', 1, rownum) ,

instr(str||',', ',', 1, rownum)

- instr(','||str, ',', 1, rownum)) province

FROM t

CONNECT BY ROWNUM<=length(str)-length(replace(str,',',''))+1;

[sql]

PROVINCE

----------------------------

广东

广西

海南

贵州

云南

如果要解决这个问题，需要对oracle的几个基本函数熟悉：

substr( string, start_position, [ length ] ),取得字符串中指定起始位置和长度的字符串

string 必需。规定要返回其中一部分的字符串。

start 必需。规定在字符串的何处开始。正数 - 在字符串的指定位置开始负数 -

在从字符串结尾的指定位置开始 0 - 在字符串中的第一个字符处开始

length 可选。规定要返回的字符串长度。默认是直到字符串的结尾。

正数 - 从 start 参数所在的位置返回负数 - 从字符串末端返回

如: substr('This is a test', 6, 2) would return 'is'

substr('This is a test', 6) would return 'is a test'

substr('TechOnTheNet', -3, 3) would return 'Net'

substr('TechOnTheNet', -6, 3) would return 'The'

substr('Thisisatest', -4, 2) would return 'te'

instr(string1,string2,start_position,nth_appearance ),返回要截取的字符串在源字符串中的位置。只检索一次，就是说从字符的开始到字符的结尾就结束。

string1 源字符串，要在此字符串中查找。

string2 要在string1中查找的字符串。

start_position 代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此

参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。

nth_appearance 代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。

select instr('aa,bb,cc,dd,ee,ff', ',', 1,1) from dual;--3

select instr('aa,bb,cc,dd,ee,ff', ',', 1,2) from dual;--6

select instr('aa,bb,cc,dd,ee,ff', ',', -1,1) from dual;--15