How to count how many 'm's exist in a row in my table in Oracle

by Aliyev M.H

This is the table definition and a sample of data.

create table newtable(
SAA varchar2(30),
s1 varchar2(1),
s2 varchar2(1),
s3 varchar2(1),
s4 varchar2(1),
s5 varchar2(1),
s6 varchar2(1),
s7 varchar2(1),
s8 varchar2(1),
s9 varchar2(1),
s10 varchar2(1)

create index newtable_index_1 on newtable(s1);
create index newtable_index_2 on newtable(s2);
create index newtable_index_3 on newtable(s3);
create index newtable_index_4 on newtable(s4);
create index newtable_index_5 on newtable(s5);
create index newtable_index_6 on newtable(s6);
create index newtable_index_7 on newtable(s7);
create index newtable_index_8 on newtable(s8);
create index newtable_index_9 on newtable(s9);
create index newtable_index_10 on newtable(s10);

insert into newtable values('Jane','m','m','g','m','j','m','m','m','n','m');

This is actually quite easy if we assume that each column can have at most one 'm' and as all columns but the first have a maximum size of one, that's a fairly safe assumption.

So how we can do this? Quite easily by stringing together a few Oracle SQL functions as follows:

SUM( sign(instr(nvl(saa,'x'),'m'))
+sign(instr(nvl(s10,'x'),'m'))) cnt_m
FROM newtable

Let's analyse this statement from the inside out.

The inner most function is NVL which is used to convert a NULL (i.e. empty) column into a value (in this case 'x'). We have to this because otherwise the instr function will return NULL instead of either 0 if 'm' is not found or 1 if it is found.

The sign function looks at the value of a number (in this case the result of instr) to see if it's positive, 0, or negative and returns 1 for a positive number, 0 if the number is zero, or -1 for a negative number. As the position of a character in a string can never be less than zero we don't need to worry about negative numbers.

Obviously for all but the first column, the position of 'm' in the column can only be 0 or 1 in this case so we don't need to use the sign function for these columns, as the instr function can therefore return only 0 or 1, but just in case we decide to increase the length of these columns in the future, let's leave the sign function in.

Now all we need to do is add them up which we do by adding the result of the sign function on each column to that of the next. This is done in SQL by using the '+' sign. This gives us the count of how many 'm's occur in each row in our table in our Oracle database and we us the sum function to give us a total number for the table.

If we can't assume that there will be at most one occurrence of 'm' in the first column then we would have to use the SQL substr function to examine the column one character at a time.

You might also find our Oracle SQL tutorials worthwhile.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Comments for How to count how many 'm's exist in a row in my table in Oracle

Average Rating starstarstarstarstar

Click here to add your own comments

Dec 21, 2011
Oracle Developer
by: Sanjay Monpara

its giving wrong result.....

use this

select length(saa||s1||s2||s3||s4||s5||s6||s7||s8||s9||s10)
length(replace(saa||s1||s2||s3||s4||s5||s6||s7||s8||s9||s10,'m','')) count_char
from newtable

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.