Error when doing to_number

[Fiona]

Hello. I am trying to right a SQL to take the first letter of a variable using SUBSTR, and convert it to number using TO_NUMBER like this:

TO_NUMBER(DECODE(SUBSTR((PSADMIN.APPOINTMENTS.WORK_PATTERN,1,1),'H',5,(SUBSTR((PSADMIN.APPOINTMENTS. WORK_PATTERN,1,1)))

SUBSTR(PSADMIN.APPOINTMENTS.WORK_PATTERN,1,1) returns the characters 1, 2, 3, 4, 5, 6, 7, H

I know in the above statement where 'H' will return 5, but there may be other alphabets returned as result of doing the SUBSTR in the future, which will cause promlems.

Is there a way to exclude all alphabetical characters from doing TO_NUMBER, instead of having to use DECODE?

Thank you for your time and help.
Regards,
Fiona

[DaPi]

TRANSLATE should be what you want: http://www.csee.umbc.edu/help/oracle...on.htm#1025232

[Fiona]

Will try it - thank you for your help.

Fiona
x