Valid Cell Phone

UDA
Enthusiast

Valid Cell Phone

Hi

Can you help with query below, I need to mark if the CellPhone is valid or not, this query does not show a cell number with less than 10 digit as invalid, please help me resolve this issues.
SELECT
OCU_N Party_Id
,OCU_CELL_PHONE_N Cell_Phone
, CASE
WHEN TRIM(OCU_CELL_PHONE_N) NOT LIKE '%_@__%.__%'
OR TRIM(OCU_CELL_PHONE_N) LIKE ANY
('%/%' ,'%/' ,'/%' ,'%\%' ,'%\' ,'\%' ,'%:%' ,'%:' ,':%' ,'%,%' ,'%,' ,',%' ,'%,%' ,'%;%' ,';%' ,'%;' ,'%~%' ,'~%' ,'%~' ,'%!%' ,'!%' ,'%!' ,'%#%' ,'#%' ,'%#' ,'%$%' ,'$%' ,'%$'
, '%@%@%' ,'@%' ,'%@' ,'.%' ,'%.' ,'-%' ,'%-' ,'%=%' ,'=%' ,'%=' ,'%+%' ,'+%' ,'%+' ,'%|%' ,'|%' ,'%|' ,'%{%' ,'{%' ,'%{' ,'%}%' ,'}%' ,'%}' ,'%[%' ,'[%' ,'%[' ,'%]%' ,']%' ,'%]'
,'%`%' ,'`%' ,'%`' , '%^%' ,'^%' ,'%^' ,'%&%' ,'&%' ,'%&' ,'%*%' ,'*%' ,'%*' ,'%(%' ,'(%' ,'%(' ,'%)%' ,')%' ,'%)' ,'% %','%"%','"%','%"','% %','%..%'
,'%a','%b%','%c%','%d%','%e%','%f%','%g%','%h%','%i%','%j%','%l%','%m%','%n%','%o%','%p%','%q%','%r%','%s%','%s%','%t%','%u%','%v%','%w%','%x%','%y%','%z%')
THEN 'Y'
ELSE 'N'
END AS Cell_Ind

FROM ODSS.O_CUST
1 REPLY
Enthusiast

Re: Valid Cell Phone

Wouldn't this work?

SELECT

OCU_N Party_Id

,OCU_CELL_PHONE_N Cell_Phone

, CASE

WHEN

(

TRIM(OCU_CELL_PHONE_N) NOT LIKE '%_@__%.__%'

OR TRIM(OCU_CELL_PHONE_N) LIKE ANY

('%/%' ,'%/' ,'/%' ,'%\%' ,'%\' ,'\%' ,'%:%' ,'%:' ,':%' ,'%,%' ,'%,' ,',%' ,'%,%' ,'%;%' ,';%' ,'%;' ,'%~%' ,'~%' ,'%~' ,'%!%' ,'!%' ,'%!' ,'%#%' ,'#%' ,'%#' ,'%$%' ,'$%' ,'%$'

, '%@%@%' ,'@%' ,'%@' ,'.%' ,'%.' ,'-%' ,'%-' ,'%=%' ,'=%' ,'%=' ,'%+%' ,'+%' ,'%+' ,'%|%' ,'|%' ,'%|' ,'%{%' ,'{%' ,'%{' ,'%}%' ,'}%' ,'%}' ,'%[%' ,'[%' ,'%[' ,'%]%' ,']%' ,'%]'

,'%`%' ,'`%' ,'%`' , '%^%' ,'^%' ,'%^' ,'%&%' ,'&%' ,'%&' ,'%*%' ,'*%' ,'%*' ,'%(%' ,'(%' ,'%(' ,'%)%' ,')%' ,'%)' ,'% %','%"%','"%','%"','% %','%..%'

,'%a','%b%','%c%','%d%','%e%','%f%','%g%','%h%','%i%','%j%','%l%','%m%','%n%','%o%','%p%','%q%','%r%','%s%','%s%','%t%','%u%','%v%','%w%','%x%','%y%','%z%')

)

and LENGTH (OCU_CELL_PHONE_N) > 9

THEN 'Y'

ELSE 'N'

END AS Cell_Ind

FROM ODSS.O_CUST

Put brackets around the first two conditions then use "length" to return only rows with 10 chars or more?

Cheers

Chris