I was wondering if someone could help me with this problem.
I have a table that contains seperate columns for First and Last names. I want to query all names that have a lowercase letter after a hyphen in either First name, Last name or both.
Is there something in Oracle that allows me to do this without brute force? Like using SUBSTR with a reference Char? ('-')
Right now all I have is this slow mess:
select FIRST_NAME, LAST_NAME
from myTABLE
where FIRST_NAME in (('%-a%'), ('%-b%'), ('%-c%'), ('%-d%'),('%-e%'), ('%-f%'), ('%-g%'), ('%-h%'), ('%-i%'),('%-j%'),('%-k%'), ('%-l%'), ('%-m%'), ('%-n%'),('%-o%'),('%-p%'),('%-q%'),('%-r%'),('%-s%'),('%-t%'), ('%-u%'), ('%-v%'),('%-w%'),('%-x%'),('%-y%'),('%-z%') )
or LAST_NAME in (('%-a%'), ('%-b%'), ('%-c%'), ('%-d%'),('%-e%'), ('%-f%'), ('%-g%'), ('%-h%'), ('%-i%'),('%-j%'),('%-k%'), ('%-l%'), ('%-m%'), ('%-n%'),('%-o%'),('%-p%'),('%-q%'),('%-r%'),('%-s%'),('%-t%'), ('%-u%'), ('%-v%'),('%-w%'),('%-x%'),('%-y%'),('%-z%') )
Try this:
with t(firstname, lastname) as (
select 'firstname1with-lowercase', 'lastname1with-lowercase' from dual union all
select 'firstname2with-Uppercase', 'lastname1with-lowercase'from dual union all
select 'firstname3with-lowercase', 'lastname1with-Uppercase'from dual union all
select 'firstname4with-Uppercase', 'lastname1with-Uppercase'from dual
)
select t.*
, case
when REGEXP_LIKE(firstname, '\-[a-z]') and REGEXP_LIKE(lastname, '\-[a-z]') then 'by firstname and lastname'
when REGEXP_LIKE(firstname, '\-[a-z]') then 'by firstname'
else 'by lastname'
end condition
from t
where REGEXP_LIKE(firstname, '\-[a-z]')
or REGEXP_LIKE(lastname, '\-[a-z]')
FIRSTNAME LASTNAME CONDITION
---------------------------------------------------------------------------------
firstname1with-lowercase lastname1with-lowercase by firstname and lastname
firstname2with-Uppercase lastname1with-lowercase by lastname
firstname3with-lowercase lastname1with-Uppercase by firstname
So [a-z] means I need one of the characters between a-z, if you work with some special symbols like å or ä or ö or other you have to use [a-zåäö]. If you want to add capital letters you have use [a-zåäöA-ZÄÖÅ] (you can add numbers or special symbols on your own). \- means that I am looking for a hyphen in the word. I can't use just the hyphen character, and I have to escape it with a the backslash symbol.
So the shown regular expression is a mask for searching words with hyphen and lowercase character right after the hyphen.
You can read more about regular expressions and their support in oracle