SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE test_table ( TELNO1 varchar(3), TELNO2 varchar(8), CELLNO1 varchar(3), CELLNO2 varchar(8) ); INSERT INTO test_table (TELNO1, TELNO2, CELLNO1, CELLNO2) VALUES ('123', '45678901', '456', '78901234'), ('456', '78901234', '789', '01234567'), ('789', '01234567', '123', '45678901'), ('123', '4567890', '456', '78901234'), ('321', '9876543', '654', '32109876'), ('111', '1111111', '222', '22222222'); SELECT CASE WHEN (LEN(RTRIM(TELNO2)) = 8) THEN REPLACE(REPLACE(RTRIM(TELNO1), '[^0-9]', ''), '', '-') + '-' + REPLACE(REPLACE(REPLACE(RTRIM(TELNO2), '[^0-9]', ''), '(.{4})(.{4})', '\1-****'), '', '') ELSE REPLACE(REPLACE(RTRIM(TELNO1), '[^0-9]', ''), '', '-') + '-' + REPLACE(REPLACE(REPLACE(RTRIM(TELNO2), '[^0-9]', ''), '(.{3})(.{4})', '\1-****'), '', '') END AS TelNo, CASE WHEN (LEN(RTRIM(CELLNO2)) = 8) THEN REPLACE(REPLACE(REPLACE(RTRIM(CELLNO1) + RTRIM(CELLNO2), '[^0-9]', ''), '(.{3})(.{4})(.{4})', '\1-\2-****'), '', '') ELSE REPLACE(REPLACE(REPLACE(RTRIM(CELLNO1) + RTRIM(CELLNO2), '[^0-9]', ''), '(.{3})(.{4})(.{4})', '\1-\2-****'), '', '') END AS CELLNo FROM test_table;