how to remove '-' in number by using case statement
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
here Is the query which iam using.........correct me If I am wrong.
SELECT acct_num CASE WHEN acct_num Is NOT NULL AND POS(acct_num,'-') Is NOT NULL THEN (SUBSTR(acct_num,1,7) || SUBSTR(acct_num_mod_num,9,12)) AS "Account num w/o '-'" ELSE acct_num END FROM hyoc_access_vws.fin_cur