DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: count the number of times a character appears in a string

  1. #1
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334

    count the number of times a character appears in a string

    Oracle 9.2.0.5

    Got a string like this in a column

    {1} at {2} {3} the defendant, being a television dealer, failed, without reasonable excuse, to give notification in the prescribed form to the BBC within 28 days of an arrangement to {4} a television set to a person by another television dealer, namely {5}.

    I only one to return rows with have 3 set's of {} so would be 3 {'s and 3 }'s

    I thought maybe instr could help, but seemingly now, anyone know how I can do it?

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    TRANSLATE the unwanted chars to NULL and then look at the length of the string - a bit messy.
    OR
    Write a function that goes through the string and counts.
    OR
    SUBSTR & INSTR nested 3 and 4 deep.

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    1* select length(replace('ngiri ii',' ','')) -length(replace(replace('ngiri ii',' ',''),'i','')) from dual
    SQL> /

    LENGTH(REPLACE('NGIRIII','',''))-LENGTH(REPLACE(REPLACE('NGIRIII','',''),'I',''))
    ---------------------------------------------------------------------------------
    4

    Developed from davey23uk 's clue

  4. #4
    Join Date
    Apr 2003
    Posts
    353
    read as DaPi's clue

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    Do you need to check for matching brackets? Something like this perhaps...
    Code:
    WHERE TRANSLATE (column_value, '#' || TRANSLATE (column_value, '#{}', '#'), '#') = RPAD ('{}', :n * 2, '{}')
    ...or maybe just...
    Code:
    WHERE INSTR (column_value, '{', 1, :n) > 0 AND INSTR (column_value, '{', 1, :n + 1) = 0;

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Originally posted by padders
    Do you need to check for matching brackets? Something like this perhaps...
    Code:
    WHERE TRANSLATE (column_value, '#' || TRANSLATE (column_value, '#{}', '#'), '#') = RPAD ('{}', :n * 2, '{}')
    ...or maybe just...
    Code:
    WHERE INSTR (column_value, '{', 1, :n) > 0 AND INSTR (column_value, '{', 1, :n + 1) = 0;
    excellent, thanks

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:
    SQLselect txtinstr(txt,'{',1,3from my_table ;

    TXT                            INSTR(TXT,'{',1,3)
    ------------------------------ ------------------
    {
    1}  {2}  {3} {4}                              11
    {1}  {2}  {3}                                  11

    SQL
    select txtinstr(txt,'{',1,3)
      
    2  from my_table
      3   where instr
    (txt,'{',1,3) > 0
      4     
    and instr(txt,'}',1,3) > 0
      5     
    and instr(txt,'{',1,4) = 0
      6  
    /

    TXT                            INSTR(TXT,'{',1,3)
    ------------------------------ ------------------
    {
    1}  {2}  {3}                                  11

    SQL
    spool off

    Is that you are looking for?

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width