CODEWITHSUNDEEP
sqloracle-databasesubstring
user1687580
user1687580

Reputation: 115

Determine if Character after Hyphen is Lowercase in Oracle SQL

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%') )

Upvotes: 0

Views: 482

Answers (1)

neshkeev
neshkeev

Reputation: 6486

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

Upvotes: 3

Related Questions