CODEWITHSUNDEEP
sqlstringoracle-databasefunctionplsql
Artem.Borysov
Artem.Borysov

Reputation: 1041

Oracle sql function: word starts with lower case 1st character, abbreviation with upper

I need to make to lower only 1st character of the string

SOME_FUNCTION('Switcher USA');

make it to lower:

select SOME_FUNCTION_1('Switcher USA') from dual;
sql -> switcher USA

Perhaps there is exist something like this function: http://www.techonthenet.com/oracle/functions/lower.php

Also, at the same time - if the word starts with the abbreviation (two or more upper-case characters in a row) - I would like to nothing happened:

select SOME_UPGRADE_FUNCTION('USA switcher') from dual;
sql -> USA switcher

What is it for? I need to use this output string in middle of the sentence something like this:

Are you sure you want to enable the switch USA?

Not this:

Are you sure you want to enable the Switch USA?

Thanks

Upvotes: 1

Views: 2823

Answers (2)

Patrick Marchand
Patrick Marchand

Reputation: 3445

Something like this?:

select case when substr(text_value, 1,2) = upper(substr(text_value, 1,2)) then text_value
            else lower(substr(text_value, 1, 1)) || substr(text_value, 2)
            end
from dual

For example:

select case when substr('USA switch', 1,2) = upper(substr('USA switch', 1,2)) then 'USA switch'
            else lower(substr('USA switch', 1, 1)) || substr('USA switch', 2)
            end
from dual
union all
select case when substr('Switch USA', 1,2) = upper(substr('Switch USA', 1,2)) then 'Switch USA'
            else lower(substr('Switch USA', 1, 1)) || substr('Switch USA', 2)
            end
from dual

Upvotes: 4

Gary_W
Gary_W

Reputation: 10360

Here's another way using just a query. It could be put into a function for reuse. Compare the first two letters to the uppercased version of the first two letters. If the same (uppercase), use as-is. If not, lowercase it.

SQL> with tbl(str) as (
     select 'USA test' from dual
     union
     select 'Test USA' from dual
     union
     select 'USAs team' from dual
   )
   select
     case
       when regexp_substr(str, '^(.){2}', 1, 1) = 
            UPPER(regexp_substr(str, '^(.){2}', 1, 1))
         THEN str
       else
         LOWER(regexp_replace(str, '^(.*)( .*)$', '\1')) || 
               regexp_replace(str, '^(.*)( .*)$', '\2')
     end converted
   from tbl;

CONVERTED
--------------------------------------------------------------------------------
test USA
USA test
USAs team

SQL>

Upvotes: 1

Related Questions