OraFAQ Forum: SQL & PL/SQL » First Character Encountered Capitalised and rest in lower

Home » SQL & PL/SQL » SQL & PL/SQL » First Character Encountered Capitalised and rest in lower (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

First Character Encountered Capitalised and rest in lower [message #617886]

Fri, 04 July 2014 03:52

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi,

I want to convert my string column such that first character encountered will be in UPPER case and rest following will be in lower case.

Test Data:

WITH tab AS
(
 SELECT 'performance By Springs blanket twinn' my_col FROM dual
 UNION ALL
 SELECT '#35 fresh kids dental set 2pc' FROM dual
 UNION ALL
 SELECT '3 musketeer candy bar 1.92oz' FROM dual
 UNION ALL
 SELECT ' values option Choose-a-Size paper towels 1 Roll 54.6 sq ft' FROM dual
)
SELECT * FROM tab;

Here the above set should change as shown in attached image.
/forum/fa/12007/0/

Thanks in advance.

Attachment: Scenario_Table.JPG
(Size: 47.80KB, Downloaded 1168 times)

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617890 is a reply to message #617886]

Fri, 04 July 2014 04:38

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

SQL> WITH tab
  2       AS (SELECT 'performance By Springs blanket twinn' my_col
  3           FROM   dual
  4           UNION ALL
  5           SELECT '#35 fresh kids dental set 2pc'
  6           FROM   dual
  7           UNION ALL
  8           SELECT '3 musketeer candy bar 1.92oz'
  9           FROM   dual
 10           UNION ALL
 11           SELECT ' values option Choose-a-Size paper towels 1 Roll 54.6 sq ft'
 12           FROM   dual)
 13  SELECT CASE
 14           WHEN Regexp_like(Substr(my_col, 1, 1), '[[:alpha:]]') THEN
 15           Initcap(Substr(my_col, 1, Instr(my_col, ' ', 1, 1)-1))
 16           ||
 17           Lower(Substr(my_col, Instr(my_col, ' ', 1, 1)))
 18           WHEN Regexp_like(Substr(my_col, 1, 1), '[[:blank:]]') THEN
 19           Substr(my_col, 1, Instr(my_col, ' ', 1, 1) - 1)
 20           ||
 21           Initcap(Substr(my_col, Instr(my_col, ' ', 1, 1),
 22                   Instr(my_col, ' ', 1, 2) -
 23                      Instr(my_col, ' ', 1, 1)))
 24                                                                      ||
 25           Lower(Substr(my_col, Instr(my_col, ' ', 1, 2)))
 26           ELSE Substr(my_col, 1, Instr(my_col, ' ', 1, 1) - 1)
 27                || Initcap(Substr(my_col, Instr(my_col, ' ', 1, 1),
 28                           Instr(my_col, ' ', 1, 2) -
 29                              Instr(my_col, ' ', 1, 1)))
 30                ||Lower(Substr(my_col, Instr(my_col, ' ', 1, 2)))
 31         END str
 32  FROM   tab
SQL> /
STR
--------------------------------------------------------------------------------
Performance by springs blanket twinn
#35 Fresh kids dental set 2pc
3 Musketeer candy bar 1.92oz
 Values option choose-a-size paper towels 1 roll 54.6 sq ft

Regards,
Lalit

Edit : Indented the code not to cross 80 characters

[Updated on: Fri, 04 July 2014 05:20]

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617903 is a reply to message #617890]

Fri, 04 July 2014 05:24

gauravgautam135
Messages: 33
Registered: December 2013

Member

Thanks Lalit !!!!

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617907 is a reply to message #617890]

Fri, 04 July 2014 05:38

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Alternatively,

orclz>
orclz> select initcap('scott') from dual;

INITC
-----
Scott

orclz>

--update: oh, right, I just saw that stuff about the first char, no matter where in the string.

[Updated on: Fri, 04 July 2014 05:40]

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617911 is a reply to message #617907]

Fri, 04 July 2014 05:53

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I used initcap, but only for the first alphabet from start of string, rest of the string in lower. The case construct is used to handle the different cases of first encounter of alphabet. Looks messy, however, should take less CPU cost than regular expressions. I used regexp only to check the character class.

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617913 is a reply to message #617911]

Fri, 04 July 2014 06:14

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi,

I tried this one.

SQL> WITH tab AS
  2  (
  3   SELECT 'performance By Springs blanket twinn' my_col FROM dual
  4   UNION ALL
  5   SELECT '#35 fresh kids dental set 2pc' FROM dual
  6   UNION ALL
  7   SELECT '3 musketeer candy bar 1.92oz' FROM dual
  8   UNION ALL
  9   SELECT ' values option Choose-a-Size paper towels 1 Roll 54.6 sq ft' FROM dual
 10   UNION ALL
 11   SELECT ' #345 values option Choose-a-Size paper towels 1 Roll 54.6 sq ft' FROM dual
 12  )
 13  SELECT   
 14           REGEXP_INSTR(my_col,'[A-Z,a-z]',1,1) First_Char_Position,
 15           CASE 
 16           WHEN REGEXP_INSTR(my_col,'[A-Z,a-z]',1,1) = 1
 17           THEN UPPER(SUBSTR(my_col,1,1))||LOWER(SUBSTR(my_col,2,LENGTH(my_col)))
 18           ELSE SUBSTR(my_col,1,REGEXP_INSTR(my_col,'[A-Z,a-z]',1,1) -1)||
 19               UPPER(SUBSTR(my_col,REGEXP_INSTR(my_col,'[A-Z,a-z]',1),1))||
 20               LOWER(SUBSTR(my_col,REGEXP_INSTR(my_col,'[A-Z,a-z]',1)+1,LENGTH(my_col)))
 21    END my_col_modified
 22   FROM tab;

FIRST_CHAR_POSITION MY_COL_MODIFIED
------------------- --------------------------------------------------------------------------------
                  1 Performance by springs blanket twinn
                  5 #35 Fresh kids dental set 2pc
                  3 3 Musketeer candy bar 1.92oz
                  2  Values option choose-a-size paper towels 1 roll 54.6 sq ft
                  7  #345 Values option choose-a-size paper towels 1 roll 54.6 sq ft

SQL>

Can you please check for any exception present?

Thanks!!

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617917 is a reply to message #617913]

Fri, 04 July 2014 07:22

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Your code works incorrectly when string has to letters:

WITH tab AS (
             SELECT '12345' my_col FROM dual
            )
SELECT   my_col,
         REGEXP_INSTR(my_col,'[A-Z,a-z]',1,1) First_Char_Position,
         CASE
         WHEN REGEXP_INSTR(my_col,'[A-Z,a-z]',1,1) = 1
         THEN UPPER(SUBSTR(my_col,1,1))||LOWER(SUBSTR(my_col,2,LENGTH(my_col)))
         ELSE SUBSTR(my_col,1,REGEXP_INSTR(my_col,'[A-Z,a-z]',1,1) -1)||
             UPPER(SUBSTR(my_col,REGEXP_INSTR(my_col,'[A-Z,a-z]',1),1))||
             LOWER(SUBSTR(my_col,REGEXP_INSTR(my_col,'[A-Z,a-z]',1)+1,LENGTH(my_col)))
  END my_col_modified
 FROM tab
/

MY_COL FIRST_CHAR_POSITION MY_COL_MODIFIED
------ ------------------- ---------------
12345                    0 112345

SQL>

Anyway,it can be do simpler without breaking string in pieces and then concatenating:

WITH tab AS (
             SELECT 'performance By Springs blanket twinn' my_col FROM dual UNION ALL
             SELECT '#35 fresh kids dental set 2pc' FROM dual UNION ALL
             SELECT '3 musketeer candy bar 1.92oz' FROM dual UNION ALL
             SELECT ' values option Choose-a-Size paper towels 1 Roll 54.6 sq ft' FROM dual UNION ALL
             SELECT '12345' FROM dual
            )
select  my_col,
        regexp_replace(
                       lower(my_col),
                       '.',
                       regexp_substr(
                                     upper(my_col),
                                     '[[:alpha:]]'
                                    ),
                       regexp_instr(
                                    my_col || 'x',
                                    '[[:alpha:]]'
                                   ),
                       1
                      ) new_my_col
  from  tab
/

MY_COL                                                      NEW_MY_COL
----------------------------------------------------------- ------------------------------------------------------------
performance By Springs blanket twinn                        Performance by springs blanket twinn
#35 fresh kids dental set 2pc                               #35 Fresh kids dental set 2pc
3 musketeer candy bar 1.92oz                                3 Musketeer candy bar 1.92oz
 values option Choose-a-Size paper towels 1 Roll 54.6 sq ft  Values option choose-a-size paper towels 1 roll 54.6 sq ft
12345                                                       12345

SQL>

SY.

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617919 is a reply to message #617917]

Fri, 04 July 2014 07:38

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

gauravgautam135 wrote on Fri, 04 July 2014 16:44

Can you please check for any exception present?

Hmm, if the data is like '12345a hi', none of our solutions seem to work.

Lalit's :

SQL> WITH tab
  2       AS (SELECT '12345a hi' my_col FROM dual)
  3  SELECT ...
 23  /
STR
---------------
12345a12345a hi

OP's :

SQL> WITH tab AS
  2      (
  3       SELECT '12345a hi' my_col FROM dual
  4     )
  5     SELECT ...
 15  /
FIRST_CHAR_POSITION MY_COL_MODIFIED
------------------- ---------------
                  6 12345A hi

SY's :

SQL> WITH tab AS (
  2               SELECT '12345a hi' my_col FROM dual
  3              )
  4  select ...
 19  /
MY_COL    NEW_MY_COL
--------- --------------------------------------------------------------------------------
12345a hi 12345A hi

@OP, so do you want the output as "12345a Hi" or the current output is fine with you?

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617922 is a reply to message #617919]

Fri, 04 July 2014 08:24

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

OP wrote "first character encountered" (I think we all agree OP meant letter not character). Based on that it should be '12345A hi'. Otherwise OP needs to clarify the rules.

SY.

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #617934 is a reply to message #617886]

Fri, 04 July 2014 10:11

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Another one.

WITH temp
        AS (SELECT 'performance By Springs blanket twinn' my_col FROM DUAL
            UNION ALL
            SELECT '#35 fresh kids dental set 2pc' FROM DUAL
            UNION ALL
            SELECT '3 musketeer candy bar 1.92oz' FROM DUAL
            UNION ALL
            SELECT ' values option Choose-a-Size paper towels 1 Roll 54.6 sq ft'
              FROM DUAL
            UNION ALL
            SELECT '12345a hi' FROM DUAL)
SELECT UPPER (SUBSTR (my_col, 1, REGEXP_INSTR (my_col, '[[:alpha:]]')))
       || LOWER (SUBSTR (my_col, REGEXP_INSTR (my_col, '[[:alpha:]]') + 1))
  FROM temp;

Manu

Report message to a moderator

Re: First Character Encountered Capitalised and rest in lower [message #618040 is a reply to message #617934]

Mon, 07 July 2014 01:32

gauravgautam135
Messages: 33
Registered: December 2013

Member

Hi Lalit, Solomon

Yes, i need to look at first letter of my string.
e.g. "1234a Hi" should change to "1234A hi"

secondly, column values not have any value pure digits. so we can exclude our case '12345' here.

However, i have used my solution already so just wanted to ask if there will be any impact/exception keeping in mind that Column values will always have atleast one letter in it.

Thanks!!! Solomon for giving smaller approach and i will keep it in mind for future scenarios.

Thanks

Report message to a moderator

Previous Topic:	BULK COLLECT -not getting expected result
Next Topic:	query to find tablename, bindvariable column names

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 18:15:08 CDT 2024