-
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?
-
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.
-
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
-
-
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;
-
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
-
PHP Code:
SQL> select txt, instr(txt,'{',1,3) from my_table ;
TXT INSTR(TXT,'{',1,3)
------------------------------ ------------------
{1} {2} {3} {4} 11
{1} {2} {3} 11
SQL> select txt, instr(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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|