Counting Specific Characters

[MonteyPython]

Is there a way I can count the number of a specific characters of a string in Oracle?

ie. conting dashes of "0052-2901-AA8KP-1" = 3

perhaps something like this?

select stringfield, Character#command(stringfield, "-") from imagination where anyhelp>0 and appreciation=(anyhelp)^2

[davide++]

Hi all.

Oracle provides standard string function, so there isn't a function to count characters.
You should create a your own PL/SQL function that does this, and call it as field of a query; for example


SELECT stringfield,
CountChar(stringfield, '-')
FROM imagination
WHERE ...

[MonteyPython]

Thanks for the reply. I'm using 3rd party software to pass this query to the database, so I dont have access to write functions. Would there be a way I would insert a recursive loop like:

for n=1 to totalcharacters
if char(n) = '-' then i=i+1
next(n)

I'm fairly amature to oracle and sql, so I need the child like explainations. :)

[MonteyPython]

Nevermind, I had a eureka moment. I only care about strings with more than 3 dashes, so I did this instead of counting them. Thanks for the help anyway!

like '%-%-%-%-%'

[davide++]

If your string is a constant (and I don't believe it), you should use a command like this:


SELECT DECODE(SUBSTR('AAA-AAA-AA-A', 1, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 2, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 3, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 4, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 5, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 6, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 7, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 8, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 9, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 10, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 11, 1), '-', 1, 0) +
DECODE(SUBSTR('AAA-AAA-AA-A', 12, 1), '-', 1, 0)
FROM DUAL


I hope this will help you (but I doubt).

[cjard]

to count the number of occurrences of a specific character is simple:


take the length of the string, and substract the length of the result of replacing all the characters youre looking for with nothing


LENGTH(str) - LENGTH(REPLACE(str, '-', ''))


Suppose a string of length 10 has 7 dashes in it
Replacing those 7 dashes with (nothing) produces a string of length 3
10 - 3 = 7, the string contained seven dashes

[davide++]

Well

Another good idea I can steal from shrewd Cjard.

But you cannot say that "it's simple": it's simple the solution, wasn't simple to find it :)