SQL Ques. - Counting characters

[mrDrive]

Hi,

I hope I'm posting this in the right spot.

I'm trying to use INSTR to count the number of characters in a string. What should be in the second parameter for it to return the position value of the last character?

Can I use INSTR for this?

Thanks in advance...

 

[Swany]

well, you can use instr(str, char, start_pos) to start
searching from a pos. This is going to end up being slow
especially for long strings.. You may want to do something
like this instead: (i just whipped this up so there
could be a typo or two)

PRAGMA RESTRICT_REFERENCES (count_chars,WNDS,WNPS);
function count_chars(pString in varchar2,
pChar in varchar2
pStartAt in number := 1,
pReadTo in number := null)
return number
cnt pls_integer := 0;
is
if pString is null then return 0;

for i in pStartAt .. length(pString) - nvl(pReadTo,0)
loop
if substr(pString, i, 1) = pChar then
cnt := cnt + 1;
end if;
end loop;
return cnt;
end;

-- you can then
-- select count_chars("abc def", " &quot from dual .
.. Eat, think and be merry .
... ....................... .

 

[zephan]

Note (from Toad's sql functions help):

INSTR(char1, char2 [,n [,m]])

If N is negative, Oracle counts and searches backward from the end of Char1.


select INSTR('abcdefgh', 'd', -1) from dual

will give you 4.

 

[karluk]

If you just want the length of the string, there's no need to mess around with the instr function. The function, length, is what you should use. For example

select length('abc') from dual;

returns 3.

 

[mrDrive]

Thanks for your help guys. I remembered the LENGTH function shortly after posting this thread last night. I thought Swany's function seemed a little cumbersome...but mighty creative!

 

[Swany]

Well, not having correctly read the initial question, I
thought you wanted to count the number of a certain
character inside of a string, not determine the length of
any string

Eg, I thought you wanted to say
"how many of the character 'a' are in the string 'aaaah'"

so if you ever have to do that, then use my function .
.. Eat, think and be merry .
... ....................... .

 

[sem]

Another variant of Swany's function is

length(string)-length(replace(string,char))

E.g. length of original string minus length of the string without specified character