i have to find out INPUT string word found within the other string that is pipe delimited,i am trying below way but it is surprisingly return 'Y' instead of 'N'.please let me know what i am doing in wrong in below cast statement.
CASE
WHEN REGEXP_INSTR('TCS|XY|XZ','CS',1,1,1,'i') > 0
THEN 'Y'
ELSE 'N'
END
Regards,
Raj
There is really no need to use regexp_instr() regular expression function. If you just need to know if a particular character literal is part of another character literal, instr() function will completely cover your needs:
with t1(col) as(
select 'TCS|XY|XZ' from dual union all
select 'TAB|XY|XZ' from dual
)
select col
, case
when instr(col, 'CS') > 0
then 'Y'
else 'N'
end as Is_Part
from t1
Result:
COL IS_PART
--------- -------
TCS|XY|XZ Y
TAB|XY|XZ N
Edit
If you need to take vertical bars into consideration - returning yes only if there is a standalone CS sub-string surrounded by vertical bars |CS| then yes, you could use regexp_instr() regular expression function as follows:
with t1(col) as(
select 'TCS|XY|XZ|' from dual
)
select col
, case
when regexp_instr(col, '(\||^)CS(\||$)', 1, 1, 0, 'i') > 0
then 'YES'
else 'NO'
end as res
from t1
Result:
COL RES
---------- ---
TCS|XY|XZ| NO
Note: If a character literal is dynamic you could use a concatenation operator || to form a search pattern '(\||^)' || <<'character literal', column or variable>> || '(\||$)'
CS is part of TCS. If there are different criteria of when to return yes and when to return no, you need to state it clearly in the question.The first field (TCS) contains CS which counts as a match.
If you want to match an entire field you can do like this:
CASE
WHEN REGEXP_INSTR('|' || 'TCS|XY|XZ' || '|' , '\|' || 'CS' || '\|',1,1,1,'i') > 0
THEN 'Y'
ELSE 'N'
END
Add the delimiter to your query string to "anchor" the search to whole fields. To be able to match the first and last field I also added the delimiter to the searched string.
| in the regex. I fixed it now and tried it, and it returns N for CS and Y for TCS.