oracle regexp_substr函数实现字符串split

背景

数据库中有一个owner字段，里面存了多个有权限用户的账号，通过,分割。现前台返回一个用户账号，需要判断该用户是否有权限。首先要把这个字段进行分割成列，然后就可以判断是否存在。

思路

• 首先oracle是可以使用regexp_substr实现按照分隔符获取元素的。

  REGEXP_SUBSTR(source_char, pattern
                [, position
                   [, occurrence
                      [, match_parameter ]
                   ]
                ]
               )

  其中：

  source_char是需要分割的字符串。

  pattern为正则表达。

  position是起始位置，默认为1。

  occurrence是表示第几个匹配组，默认为1。

  match_parameter是匹配参数：

  • i：大小写不敏感
  • c：大小写敏感
  • n：允许(.)匹配换行符
  • m：oracle将把字符串当做多行字符处理，^和$是起始和结束
  • x：忽略空格

  https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm

  于是我们通过下面的表达式获取分割后的某个值：

  select regexp_substr('1,2,3', '[^,]+', 1, 2) from dual; --获取第二个数

  结果如下：

  

  现在我们需要把其中的occurrence变成一个和分割后等长的列即可。

• 获取分割后元素的个数

  使用length()可以获取字符串的个数，而字符串是通过,分割的。使用replece()可以替换删除所有的分割符。分割后的字符串个数即是两者的差加1。

  select length('1,2,3') -  length(replace('1,2,3',',')) + 1 from dual;

  结果如下：

  

• 获取一个空列

  可以通过level加上connect by来获取一个空列：

  select level from dual connect by level <= 5;

  结果如下：

  

• 把上述连起来就可以获取分割后的列了。

结论

虽然有点长，但是总体思路还是比较清晰的。代码如下：

select regexp_substr('1,2,3','[^,]+' , 1, level) from dual connect by level <= (select length('1,2,3') -  length(replace('1,2,3',',')) + 1 from dual);

结果如下：

最后我们需要判断某个人是否有权限，即某人的账号是否在我们获取的分割后的列里面。直接用count(1)是否大于0即可。

select count(1) 
	from (select regexp_substr('1,2,3','[^,]+' , 1, level) as owner
		from dual connect by level <= (select length('1,2,3') - 
        	length(replace('1,2,3',',')) + 1 from dual) t
where t.owner = i_owner