Steps:
-----
1) check for empty or NULL value.
2) check if size is greater than 10.
a) size > 10, format with extension
b) else, format as a 10 digit phone number
select
case when len(str_replace(phone_no,'',NULL)) = NULL
then ''
when len(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL)) > 10
then
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7, 4)
|| ' ext:' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 10,5)
else
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7,4)
end as PHONE_NO
from person
I have not checked if Sybase supports regular expression, in which case it might be much simpler. Please share if you have a better solution.
No comments:
Post a Comment