[SOLVED] How to search for substring with following criterias?

Issue

This Content is from Stack Overflow. Question asked by Roshan

Table:
| String | Substring|
| ——– | ———|
| Tattinger | TT |
| TT’s | TT |
| TT Tattinger | TT |
| Tattinger TTa | TT |
| TTinger tab | TT |

Criterias:

  1. Substring can not be in the middle of string.
  2. Substr should always be at the start of each letters
  3. If there is any non-characters(‘.,#) after substring, that’s okay.
  4. All other cases No

Result
| String | Substring| Result|
| ——– | — |——-|
| Tattinger | TT |No |
| TT’s tattinger| TT |Yes |
| TT Tattinger | TT |Yes |
| Tattinger TTa | TT |Yes |
| TTinger tab | TT |Yes |

What I've explored so far:
1. STRING LIKE '%' || SUBSTRING || '%'----(not suitable because it matches anywhere)
2. REGEXP_SUBSTR(STRING, SUBSTRING || '\W') --(suitable partially)



Solution

How about a simple case statement? I am using like but if case isn’t important change that to ilike

case when str like 'TT%' or str like '% TT%' then 'Yes' else 'No' end as flag


This Question was asked in StackOverflow by Roshan and Answered by Phil Coulson It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?