CODEWITHSUNDEEP
sqloracle-database
rajputhch
rajputhch

Reputation: 627

oracle searching word within the input string

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

Upvotes: 1

Views: 165

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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>> || '(\||$)'

Upvotes: 3

Klas Lindb&#228;ck
Klas Lindb&#228;ck

Reputation: 33273

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.

Upvotes: 1

Related Questions