Operating System - HP-UX
1748230 Members
4163 Online
108759 Solutions
New Discussion юеВ

Re: How to capitalize a string in ORACLE?

 
SOLVED
Go to solution
Enrico Venturi
Super Advisor

How to capitalize a string in ORACLE?

Hello collegues,
a maybe stupid question:
I've the user name in lower case, but it's stored inside the database in upper case; how can I capitalize it in order to use inside a query?
I can do it outside ORACLE or inside (i.e. in sqlplus).

What's the best way?

thanks a lot
Enrico
6 REPLIES 6
Pete Randall
Outstanding Contributor
Solution

Re: How to capitalize a string in ORACLE?

Enrico,

Would the "tr" command work?

tr "[:lower:]" "[:upper:]"

Pete

Pete
Orhan Biyiklioglu
Respected Contributor

Re: How to capitalize a string in ORACLE?

You can do it inside or outside of oracle.

If you have the usernames in a file

cat filename | awk '{print toupper($0)}'

should do the job or better you can use them in the query like

...WHERE user = UPPER('&user')...

hth
Sandman!
Honored Contributor

Re: How to capitalize a string in ORACLE?

Enrico,

How about using the string function upper() within SQL*Plus for ex:

SQL> select * from where upper(USER) =
/

Indira Aramandla
Honored Contributor

Re: How to capitalize a string in ORACLE?

Hi Venturi,

You can use the UPPER and LOWER functions of oracle when you want to user upper-case or lower-case characters in your query.

For eg:
SQL> select upper('ent') from dual;

UPP
---
ENT

SQL> select lower('AAA') from dual;

LOW
---
aaa

To select and compare with upper-case name
then
SQL>select col1, col2......
from tab1
where col2 = upper('string');


Indira A
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: How to capitalize a string in ORACLE?

hi enrico,

better do it in the database in case your number of records is not much and performance is not an issue.

e.g.

select *
from dba_users
where lower(username)='&username';

or

select *
from dba_users
where username= upper('&username');

there is also soundex function which you use in other cases:
select * from t where soundex_column = soundex( 'some word' )


hope this helps too!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Arturo Galbiati
Esteemed Contributor

Re: How to capitalize a string in ORACLE?

Hi Enrico,
you could use the function upper() to capitalize string you are searching in SQL.
Take care that if teh field you are searcing are indexed this may casue a delay in the query.

My suggestion is to capitalize it outside SQL using:
typeset -u User_In_Uppercase=

HTH,
Art