Oracle - REGEXP_LIKE not giving desired results

user_12196056 0
Viewed 44

Have a string column - COL1,containing words of different lengths separated by space,this can contain special characters as well.While deriving below logic, getting different result.

Logic: If COL1 starts with an alphabet and ends with an alphabet and doesn't contain any special characters(can contain spaces in between) flag is 1 else flag is 0.


COL1            FLAG
Abc gty xyz      1
ab/c bpl ?t      0
qwr .p           0
tiotio iop       1
TItio  io/       0
abc              1
abc     xy       1

tried with

SELECT CASE WHEN REGEXP_LIKE(COL1,'^[a-zA-Z]$') THEN '1' ELSE '0' END AS FLAG FROM TABLE1;

but wrong outcome.

case oracle10g oracle sql regex
1 Answers

Would this fit your need?

select 
    col1, 
    case when regexp_like(col1, '^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$') 
        then 1 
        else 0 
    end as flag 
from table1

The regex describes a string that starts with an alphabet character, followed by 0 to N alphabet characters or spaces, and ends with an alphabet character as well.

Demo on DB Fiddle:

COL1        | FLAG
:---------- | ---:
Abc gty xyz |    1
ab/c bpl ?t |    0
qwr .p      |    0
titio  iop  |    1
TItio  io/  |    0
abc         |    1
abc     xy  |    1
user_106767160
Related