×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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?

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

You could try:

Select Initcap('MIKE LACEY') From DUAL;

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.

Will that do?


Mike
Mike_Lacey@Cargill.Com
Cargill's Corporate Web Site
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?"

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

If you really need only the first character in uppercase, try:
select upper(substr(a.column_a, 1, 1)) ¦¦ lower (substr(a.column_a, 2))
from   table_a a;

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

Mike,

You could try to write your own funtion:

CREATE OR REPLACE MYFUNC (field IN CHAR)
RETURN CHAR
IS
BEGIN
DECLARE retval IN CHAR
FOR i IN LENGTHB(field)LOOP
  IF i = 1 THEN
    SELECT retval + UPPER(SUBSTR(field, i, 1)) INTO retval;
  ELSE
    SELECT retval + LOWER(SUBSTR(field, i, 1)) INTO retval;
  END IF
LOOP;
RETURN retval;
END;

Please forgive any syntax errors. But the the you can just use your function in your query

SELECT
col1,
col2,
MYFUNC(col3),
col4
FROM tablename;

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

How about using these Oracle-provided functions:  substr, upper, lower?
Assume you have table T1 with column C1, having 2 records:


    desc T1:
     Name                            Null?    Type
     ------------------------------- -------- ----
     C1                                       VARCHAR2(20)

    select * from t1;
       C1
       --------------------
       HI THERE THIS'a TEST
       A

Then just call these 3 functions like:

    select  c1,
            upper(substr(c1,1,1)) ¦¦ lower(substr(c1,2)) c2
    from t1;

Results:

    C1                      C2
    --------------------    --------------------
    HI THERE THIS'a TEST    Hi there this'a test
    A                       A

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close