Multiple REPLACE functions inside LIKE comparison query in SQL

Question

I am working in Oracle environment. I need to build query that would search through badly formatted telephone data. So far I got :

select * from guest_db
REPLACE(REPLACE(REGEXP_REPLACE(TEL1, '[[:space:]]*',''),'+',NULL),')',NULL),'(',NULL
LIKE '% 
REPLACE(REPLACE(REPLACE(REXEXP_REPLACE('(0)1111 111111','[[:space:]]*',''),'+',NULL),')',NULL)'(',NULL)
||%';

difficulty is putting second nested replace(replace function between '% %' signs without turning them into strings. If I build this function without those percentage signs LIKE function behaves like equal sign. It spits back only exact match ( obviously without special characters like ),(, +, , )... Any help greatly appreciated.

By "search through" do you mean only find them? Do you also want to fix them? If you want to fix them, can you also describe what makes the phone number valid - for example 999-999-9999 or whatever. — Ed Gibbs
– Ed Gibbs, Commented Apr 26, 2013 at 13:22
If I put 111 I want to return records like 0-5555-111 as well as 111 111 111. If you put 111 in this query it will return only exact match 111 even though it has LIKE. — Jestem_z_Kozanowa
– Jestem_z_Kozanowa, Commented Apr 26, 2013 at 13:29
OK, solved I had to concate function with '%'|| replace(replace(... ||'%' and jump out of the string. :) — Jestem_z_Kozanowa
– Jestem_z_Kozanowa, Commented Apr 26, 2013 at 13:38

Jestem_z_Kozanowa · Accepted Answer · 2013-06-27 10:20:30Z

0

OK, solved I had to concate function with '%'|| replace(replace(... ||'%' and jump out of the string

answered Jun 27, 2013 at 10:20

Jestem_z_Kozanowa

6473 gold badges15 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple REPLACE functions inside LIKE comparison query in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related