当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Select only explicit value/regexp

问题描述:

I'm trying to select a specific value from my column. Probably It should be done by regexp function, but I have been having some problems with that...

Column name: Column1

Value1: 'Value\sdf90df\015'

Value2: 'Value\f213jdj\012'

Value3: 'Value\kjsd2ej\031'

Is there some way to select only: sdf90df, f213jdj, kjsd2ej?

网友答案:

You can use this version.

select regexp_replace( Column1, '.+\\([^\\]+)\\.+', '\1' )
  from yourTable

What I'm doing is selecting your Column1 with a function that does a replace based on a regular expression regexp_replace

The regular expression I used is .+\\([^\\]+)\\.+ breaking it you have:

.+\\ - this gets everything until find a \ we use two \\ because it need to be scaped.

([^\\]+) - This portion is whithin parenthesis to be understood as a capturing group (I will talk about it later) I'm saying here get everything that isn't a \

And

\\.+ get a dash and everything else

The regexp_replace as I'm using it has this parameters: regexp_replace(source, regex, replacement) so in my replacement I used the string \1 which means get from that regular expression just the group 1

So, your string is 'Value\sdf90df\015' breaking it on the regular expression you have:

Value\ then sdf90df which is group 1 (because of the parentesis) and then \015

On the first version of this answer I've used $1 thats because some regular expression processors use it insted of \1 but for oracle the right one is \NUMBER to specify a capturing group.

Hope the explanation had clarify your doubts.

网友答案:

You can do this with regex_substr(). Something like this:

select replace(regex_substr(column1, '[\\][^\\]+[\\]', 0, 2), '\\', '')
分享给朋友:
您可能感兴趣的文章:
随机阅读: