Counting the number of occurrences of a specific character is a column

From: Greg <esabens_at_yahoo.com>
Date: 7 Dec 2005 12:56:54 -0800
Message-ID: <1133989014.481635.282260@g49g2000cwa.googlegroups.com>

I don't know if such a function/command exists in Oracle SQL, but if there is a better way for me to do what I show below, I'd like to know.

In the Oracle database we use here at work, there is a column in a table where each character in a field of 108 characters represent a 5-minute block of time (9 hours in a work day including the lunch hour). For example:
Appt.Availtime = '00001111222200055500000011111000555...(and so on)'

The characters in the field represent what type of appointment occurred during those 5-minute time blocks. For example:

'0' = normal appointment
'1' = canceled appointment
'2' = moved appointment
'5' = no show

Since there are 108 5-minute time blocks in a normal work day (including the lunch time hour), I am adding together 108 case statements, multplying by 5 and dividing by 60 to get the number of hours each day of each of the types of appointments: (case when substr(Appt.Availtime,1,1)='0' then '1' else '0' end
+ case when substr(Appt.Availtime,2,1)='0' then '1' else '0' end
+ case when substr(Appt.Availtime,3,1)='0' then '1' else '0' end
+ case when substr(Appt.Availtime,4,1)='0' then '1' else '0' end

...
...
...
...
...


+ case when substr(Appt.Availtime,105,1)='0' then '1' else '0' end

+ case when substr(Appt.Availtime,106,1)='0' then '1' else '0' end

+ case when substr(Appt.Availtime,107,1)='0' then '1' else '0' end
+ case when substr(Appt.Availtime,108,1)='0' then '1' else '0'
end)*5/60) as NormalAppt

Then I repeat the lines but test for '1', then '2', then '5'.

My question (since I'm only ~9 months new to SQL) is if there is a better way of doing this. My query ends up being at least 432 lines long just in these case statements. Is there any Oracle SQL function/statement that would go through an entire field and count up the number of occurrances of a specified character, such as somethin like:
counting_function(table.column,'character')

Thanks in advance for any comments, suggestions or advice!!

Greg Sabens Received on Wed Dec 07 2005 - 14:56:54 CST