This discussion is archived
11 Replies Latest reply: Feb 8, 2013 4:57 AM by 971895 RSS

Count the Upper and lower case letters.

971895 Journeyer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Thank lot Karthik....
  • 5. Re: Count the Upper and lower case letters.
    971895 Journeyer
    Currently Being Moderated
    Thank lot to all
  • 6. Re: Count the Upper and lower case letters.
    Purvesh K Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Hey guys really great helping..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points