Velu Velu - 2 months ago 14
SQL Question

How to fetch special char with five digits no in oracle?

I am trying to fetch :with five digits no values in oracle.
my data look this format.

this :years 2016 only department :12345
aign:BR-PESP-THELIMITED-:30510
:thiss and :years 2016 only department :12345
travel visa :34322 LLL001


i am using this query


substring(check1,regexp_instr(check1,':'),6)


my current o/p:
years
BR-PE
thiss
34322

my expected o/p
12345
30510
12345
34322

Answer

If you want to return the five digits after a colon:

select regexp_substr(check1, ':([0-9]{5})', 1, 1)