how to remove '-' in number by using case statement

Analytics

how to remove '-' in number by using case statement

Hi,

I have a problem here,Iam new to Teradata environment

I've number here Account number which contains 12 digits,in 12 digits 8th place occupies '-'in few numbers and rest of the numbers displays normal digit in 8th place.

so my user wants to remove the '-'where ever it displays and if'-'is not there on 8th place,then number shuld display complete 12 digit number.

Please suggest me

Thanks in advance

1 REPLY
Enthusiast

Re: how to remove '-' in number by using case statement

Kumar,

This should do it:

select case when substr(acct_nbr, 8, 1) = '-'
then substr(acct_nbr, 1, 7) || substr(acct_nbr, 9, 4)
else acct_nbr
end

Regards,
Barry