All Forums Analytics
Meenakshi S 3 posts Joined 09/08
11 Nov 2008
Remove '-' from a column

I have a phone number column which has records like '111-222-333' . I need to remove the '-' from each phone number. Any suggestions?

Adeel Chaudhry 773 posts Joined 04/08
11 Nov 2008

Hello,You can use SUBSTR and concatenation to get the desired output. Example:SELECT SUBSTR('111-222-333', 1, 3) || SUBSTR('111-222-333', 5, 3) || SUBSTR('111-222-333', 9, 3);Output: 111222333HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Meenakshi S 3 posts Joined 09/08
11 Nov 2008

Hi Adeel,Thanks for your reply. But the record i have given was just an example. The column is a phone number field and it can have '-' in any position. It is not predictive. Hence i want to remove the '-' present in the phone number field and compare it with other field.Any suggestions?Thanks,Meena

Adeel Chaudhry 773 posts Joined 04/08
11 Nov 2008

Well for two "-", you can use following:SELECTB AS PhoneNumberFROM(SELECTSUBSTR('111-222-333', 1, INDEX('111-222-333', '-') - 1) || SUBSTR('111-222-333', INDEX('111-222-333', '-') + 1) AS A,SUBSTR(A, 1, INDEX(A, '-') - 1) || SUBSTR(A, INDEX(A, '-') + 1) AS B) Alias1HTH.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

HaRsHaD 8 posts Joined 11/08
11 Nov 2008

select (tmp.a||tmp.b||tmp.d)from(select position('-' in '111-222-333') pos1 ,substr('111-222-333',1,(pos1-1)) a ,substr('111-222-333',(pos1+1),position('-' in substr('111-222-333',(pos1+1)))-1) b ,substr('111-222-333',(pos1+1)) c ,substr(c,(pos1+1)) d) tmp

ANIMESH.DUTTA 35 posts Joined 05/09
05 Jun 2009

/* UNDERSTAND THE STORED PROCEDURE & WRITE YOUR OWN *//* Create Procedure to Replace unwanted Characters from a Field */REPLACE PROCEDURE ETLPROC_DEV.NAD_REPLACE(IN in_VAR1 VARCHAR(20),OUT out_VAR2 VARCHAR(30)) /* */BEGIN/* Declare Variable for each Column */DECLARE INDX INTEGER;DECLARE STR VARCHAR(30);SET INDX = 1;SET STR = '';WHILE INDX<=CHARACTERS(TRIM(in_VAR1)) DO IF SUBSTR(in_VAR1,INDX,1) = '-' /* Mention the Characters to be Removed */ THEN SET INDX = INDX+1; ELSE SET STR=STR || SUBSTR(in_VAR1,INDX,1); SET INDX = INDX+1; END IF; END WHILE;SET out_VAR2= STR;END;/* Able to Verify your Procedure by Inputting One Value */CALL ETLPROC_DEV.NAD_REPLACE ('111-777-000',A);/* Create Procedure to Replace unwanted Characters from Entire Column Using Upper Procedure */REPLACE PROCEDURE ETLPROC_DEV.NAD_CLEANUP()BEGIN/* Declare Variable for each Column */DECLARE V_FNAME VARCHAR(50); DECLARE V_LNAME VARCHAR(50);FOR NAMECURSOR AS X CURSOR FOR /* SQL - For Which Columns Need to Replace Characters */ SELECT MBR_KEY, FRST_NM, LAST_NM /* SELECT Primary Index, Columns for Cleanup */ FROM ETL_TEMP_DEV.EE_MBR_NAME /* FROM */ WHERE MBR_KEY >= 0 AND MBR_KEY */ DO CALL ETLPROC_DEV.NAD_REPLACE(NAMECURSOR.FRST_NM,V_FNAME); /* Calling Upper Procedure for the 1st Column */ CALL ETLPROC_DEV.NAD_REPLACE(NAMECURSOR.LAST_NM,V_LNAME); /* Calling Upper Procedure for the 2nd Column */ INSERT INTO ETL_TEMP_DEV.EE_MBR_NAME VALUES (NAMECURSOR.MBR_KEY, V_FNAME, V_LNAME); /* Insert Cleaned Values in Different Columns */ END FOR;END;/* Call This Procedure to get Desired Fields for Desired Columns */CALL ETLPROC_DEV.NAD_CLEANUP();

Animesh Dutta
Teradata Certified Master

gg 12 posts Joined 10/04
05 Jun 2009

If you've got the free Oracle UDFs (http://www.teradata.com/DownloadCenter/Download.aspx?ForumID=137&TopicID=9228) installed on your system you can use udf.oreplace():select '123-456-789' as col1,udf.oreplace(col1,'-');col1 oreplace('123-456-789','-')123-456-789 123456789

You must sign in to leave a comment.