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

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 SQL functions as follows:

SELECT SUM(sign(instr(nvl(saa,'x'),'m')
+sign(instr(nvl(s1,'x'),'m')
+sign(instr(nvl(s1,'x'),'m')
+sign(instr(nvl(s2,'x'),'m')
+sign(instr(nvl(s3,'x'),'m')
+sign(instr(nvl(s4,'x'),'m')
+sign(instr(nvl(s5,'x'),'m')
+sign(instr(nvl(s6,'x'),'m')
+sign(instr(nvl(s7,'x'),'m')
+sign(instr(nvl(s8,'x'),'m')
+sign(instr(nvl(s9,'x'),'m')
+sign(instr(nvl(s10,'x'),'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 the position of 'm' in the column 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 nnever 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, but just in case we decide to increase the length of these columns in our table in future, let's leave the 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. We achive this in SQL by using the '+' sign. That gives us the count of how many 'm's occur in one row in our table in our database and we sum the results for each row to give us a total number for the table.

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