Archives of the TeradataForum

Message Posted: Tue, 24 Apr 2007 @ 21:42:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I don't about clever, but this might work: for each position in the field, turn it into 1-9 or an empty string if not a number, concatenate the results, and cast as integer.

Something like

     Select cast(
     case when substr(phone_number,1,1)  between '1' and '9' then
     substr(phone_number,1,1)  else '' end
     ||
     case when substr(phone_number,2,1)  between '1' and '9' then
     substr(phone_number,2,1)  else '' end
     case when substr(phone_number,3,1)  between '1' and '9' then
     substr(phone_number,3,1)  else '' end
     ||
     case when substr(phone_number,4,1)  between '1' and '9' then
     substr(phone_number,4,1)  else '' end
     ||
     ... Etc....
     case when substr(phone_number,,1)  between '1' and '9'
     then substr(phone_number,,1)  else '' end
     As integer)

Example:

     select cast(
     case when substr('1/2',1,1) between '1' and '9' then substr('1/2',1,1)
     else '' end
     ||
     case when substr('1/2',2,1) between '1' and '9' then substr('1/2',2,1)
     else '' end
     ||
     case when substr('1/2',3,1) between '1' and '9' then substr('1/2',3,1)
     else '' end
     as integer) as now_a_number;


     now_a_number
     12

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>