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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 7 Dec 2005 22:13:37 +0100
Message-ID: <43975085$0$5767$626a14ce@news.free.fr>

"Greg" <esabens_at_yahoo.com> a écrit dans le message de news: 1133989014.481635.282260_at_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
|

select length(translate(Appt.Availtime,'0125','0'))*5/60 "NormalAppt",
       length(translate(Appt.Availtime,'1250','1'))*5/60 "CancelAppt",
       length(translate(Appt.Availtime,'2501','2'))*5/60 "MovedAppt",
       length(translate(Appt.Availtime,'5012','5'))*5/60 "NoShow"

from Appt ...

About translate:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions150a.htm#79574

Regards
Michel Cadot Received on Wed Dec 07 2005 - 15:13:37 CST