How to count how many 'm's exist in a row in my table in Oracle
by Aliyev M.H
(Baku,Azerbaijan)
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:
SELECT
SUM( sign(instr(nvl(saa,'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'))) 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.