SQL .how to convert a string in capitals to small letters?

[wharton]

How do i get a string value from a table which is in capitals and convert only the first letter to capital and the rest to small?

 

[MikeLacey]

You could try:<br><br><FONT FACE=monospace><b>Select Initcap('MIKE LACEY') From DUAL;</font></b><br><br>You'll notice that this *nearly* does what you're asking. It capitalizes the first letter of every word in the string - rather than just the first letter of the string.<br><br>Will that do?<br><br><br> <p>Mike<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Please don't send me email questions without posting them in Tek-Tips as well. Better yet -- Post the question in Tek-Tips and send me a note saying "Have a look at so-and-so in the thingy forum would you?"

 

[yaffle]

If you really need only the first character in uppercase, try:<br><FONT FACE=monospace>select upper(substr(a.column_a, 1, 1)) ¦¦ lower (substr(a.column_a, 2))<br>from&nbsp;&nbsp;&nbsp;table_a a;</font>

 

[Packermann]

Mike,<br><br>You could try to write your own funtion:<br><br>CREATE OR REPLACE MYFUNC (field IN CHAR)<br>RETURN CHAR<br>IS<br>BEGIN<br>DECLARE retval IN CHAR<br>FOR i IN LENGTHB(field)LOOP<br>&nbsp;&nbsp;IF i = 1 THEN<br>&nbsp;&nbsp;&nbsp;&nbsp;SELECT retval + UPPER(SUBSTR(field, i, 1)) INTO retval;<br>&nbsp;&nbsp;ELSE<br>&nbsp;&nbsp;&nbsp;&nbsp;SELECT retval + LOWER(SUBSTR(field, i, 1)) INTO retval;<br>&nbsp;&nbsp;END IF<br>LOOP;<br>RETURN retval;<br>END;<br><br>Please forgive any syntax errors. But the the you can just use your function in your query<br><br>SELECT<br>col1,<br>col2,<br>MYFUNC(col3),<br>col4<br>FROM tablename;

 

[Guest_imported]

How about using these Oracle-provided functions:&nbsp;&nbsp;substr, upper, lower?<br>Assume you have table T1 with column C1, having 2 records:<br><br><FONT FACE=monospace><br>&nbsp;&nbsp;&nbsp;&nbsp;desc T1:<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Null?&nbsp;&nbsp;&nbsp;&nbsp;Type<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;------------------------------- -------- ----<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VARCHAR2(20)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;select * from t1;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;--------------------<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;HI THERE THIS'a TEST<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A<br></font><br>Then just call these 3 functions like:<br><FONT FACE=monospace><br>&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;&nbsp;c1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font color=red>upper</font>(<font color=red>substr</font>(c1,1,1)) ¦¦ <font color=red>lower</font>(<font color=red>substr</font>(c1,2)) c2<br>&nbsp;&nbsp;&nbsp;&nbsp;from t1;<br></font><br>Results:<br><FONT FACE=monospace><br>&nbsp;&nbsp;&nbsp;&nbsp;C1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;C2<br>&nbsp;&nbsp;&nbsp;&nbsp;--------------------&nbsp;&nbsp;&nbsp;&nbsp;--------------------<br>&nbsp;&nbsp;&nbsp;&nbsp;HI THERE THIS'a TEST&nbsp;&nbsp;&nbsp;&nbsp;Hi there this'a test<br>&nbsp;&nbsp;&nbsp;&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A<br></font>