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: 111222333

HTH.

Regards,

Adeel

Well for two "-", you can use following:

SELECT
B AS PhoneNumber
FROM
(
SELECT
SUBSTR('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
) Alias1

HTH.

Regards,

Adeel

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

/* 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();

If you've got the free Oracle UDFs (http://www.teradata.com/DownloadCenter/Download.aspx? ForumID=137&TopicID...) 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