11 Replies Latest reply: Feb 8, 2013 6:57 AM by 971895 RSS

    Count the Upper and lower case letters.

    971895
      Hi,

      I have string like" Test1Tab", how to find how many upper and lower case letters in the string

      Thanks
        • 1. Re: Count the Upper and lower case letters.
          Karthick_Arp
          968892 wrote:
          Hi,

          I have string like" Test1Tab", how to find how many upper and lower case letters in the string

          Thanks
          Use TRANSLATE and LENGTH functions
          with t
          as
          (
          select'Test1Tab' str
            from dual
          )  
          select length(translate(str, '.abcdefghijklmnopqrstuvwxyz1234567890', '.')) upper_count,
                 length(translate(str, '.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '.')) lower_count
            from t;
          
          UPPER_COUNT LOWER_COUNT
          ----------- -----------
                    2           5 
          And by using REGEXP_REPLACE
          with t
          as
          (
          select'Test1Tab' str
            from dual
          )  
          select length(str) - length(regexp_replace(str, '[[:upper:]]')) upper_count,
                 length(str) - length(regexp_replace(str, '[[:lower:]]')) lower_count
            from t;
          
          UPPER_COUNT LOWER_COUNT
          ----------- -----------
                    2           5 
          And if you are in 11g and above DB then you can use REGEXP_COUNT
          • 2. Re: Count the Upper and lower case letters.
            Purvesh K
            968892 wrote:
            Hi,

            I have string like" Test1Tab", how to find how many upper and lower case letters in the string

            Thanks
            Works 11g onwards:
            with data as
            (
              select 'SomeString WITH Uppercase and LowerCase data.' col from dual
            )
            select regexp_count(col, '[A-Z]') upper_count, regexp_count(col, '[a-z]') lower_count
              from data;
            
            UPPER_COUNT LOWER_COUNT 
            ----------- -----------
            9           30 
            Edited by: Purvesh K on Feb 8, 2013 4:16 PM
            --Added Output.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
            • 3. Re: Count the Upper and lower case letters.
              Karthick_Arp
              Purvesh K wrote:
              968892 wrote:
              Hi,

              I have string like" Test1Tab", how to find how many upper and lower case letters in the string

              Thanks
              Works 11g onwards:
              with data as
              (
              select 'SomeString WITH Uppercase and LowerCase data.' col from dual
              )
              select regexp_count(col, '[A-Z]') upper_count, regexp_count(col, '[A-Z]') lower_count
              from data;
              I guess its just a typo.. for lower_count you have specified '[A-Z]'
              • 4. Re: Count the Upper and lower case letters.
                971895
                Thank lot Karthik....
                • 5. Re: Count the Upper and lower case letters.
                  971895
                  Thank lot to all
                  • 6. Re: Count the Upper and lower case letters.
                    Purvesh K
                    Yes, Copy and Paste error.

                    I realized and rectified just by the time you posted. :)
                    • 7. Re: Count the Upper and lower case letters.
                      Etbin
                      No need to post the same solution, just hoping the OP doesn't say (s)he meant distinct letters ;)

                      Regards

                      Etbin
                      • 8. Re: Count the Upper and lower case letters.
                        BluShadow
                        Or another way...
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1  with data as
                          2  (
                          3    select 'SomeString WITH Uppercase and LowerCase data.' col from dual
                          4  )
                          5  select utl_match.edit_distance(upper(col),col) as lower_cnt
                          6        ,utl_match.edit_distance(lower(col),col) as upper_cnt
                          7* from data
                        SQL> /
                        
                         LOWER_CNT  UPPER_CNT
                        ---------- ----------
                                30          9
                        • 9. Re: Count the Upper and lower case letters.
                          Etbin
                          Thanks for your post.
                          Having to deal principally with numbers seems to have crippled me in a way.

                          Regards

                          Etbin
                          • 10. Re: Count the Upper and lower case letters.
                            chris227
                            Be careful
                            -- NLS_SORT BINARY
                            with t as (
                            select 'TeséÉtTab' str from dual
                            )  
                            select
                             length(str) l
                            ,length(translate(str, '.ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', '.')) u_t
                            ,length(translate(str, '.abcdefghijklmnopqrstuvwxyz1234567890', '.')) l_t
                            ,regexp_count(str, '[A-Z]') u_rr
                            ,regexp_count(str, '[a-z]') l_rr
                            ,regexp_count(str, '[[:upper:]]') u_rc
                            ,regexp_count(str, '[[:lower:]]') l_rc
                            ,utl_match.edit_distance(lower(str),str) u_u
                            ,utl_match.edit_distance(upper(str),str) l_u
                            ,dump(str, 1010) d
                            from t;
                            
                            
                            L U_T L_T U_RR L_RR U_RC L_RC U_U L_U D 
                            9 7 4 2 5 3 6 3 6 Typ=96 Len=11 CharacterSet=AL32UTF8: 84,101,115,195,169,195,137,116,84,97,98 
                            Edited by: chris227 on 08.02.2013 04:04
                            included utl_match.edit_distance

                            Edited by: chris227 on 08.02.2013 04:07
                            • 11. Re: Count the Upper and lower case letters.
                              971895
                              Hey guys really great helping..