pattern matching problem

[divyakrishnan]

Hi...
I have a table named empl with following fields(eid,ename,esal)

I had inserted the following records to empl table

1 Govind Raj 20000
2 GovindRaj 600000
3 Govindraj 10000
4 govindraj 50000
5 Govind.Raj 50000
6 Govind-Raj 20000
7 Govind_raj 2000
8 Govind.raj.3000
9 govind,raj. 4000
10 Govind'raj 6000
11 Arun 6000
12 Deepthy 6000

I want to list all the records with ename govindraj (by avoding space ,comma,dot etc).That is all the records with following names
Govind Raj ,
GovindRaj ,
Govindraj
govindraj
Govind.Raj
Govind-Raj
Govind_raj
Govind.raj.
govind,raj.
Govind'raj

What is the query for this result?Heard about REGEXP.But I don't know how to write the query..

[debasisdas]

what is the query that you are working on.

[divyakrishnan]

The query I used is shown below,

SELECT * 
FROM emp
WHERE ename LIKE '%govindraj%'
OR ename LIKE '%/-%'
ESCAPE '/'
OR ename LIKE '%/.%'
ESCAPE '/'
OR ename LIKE '%/_%'
ESCAPE '/'

using this query I got all records except first (which is having space character)

[debasisdas]

try this sample code

select * from empl 
where lower(replace(translate(ename,'., _-','     '),' ',''))= 'govindraj'

you need to modify the above query to suit your requirements.
also check for MySQL syntax.

[divyakrishnan]

I am getting the error like

FUNCTION employ.translate does not exist

[divyakrishnan]

I am using XAMPP's phpmyadmin.Also I tried it with SQL Server2005.There also I am getting the error like
"traslate is not a recognized built-in function name"

[debasisdas]

that is what i said in my post.

it is a sample code so i said check for MySQL syntax.

NOTE :-- the code is in Oracle syntax.

[divyakrishnan]

Can I solve this using REGEXP?

[debasisdas]

try this

SELECT  * from empl 
  where  LOWER(REPLACE(replace(replace(replace(replace(drug_name,'.',' '),'_',' '),'-',''),',',' '),' ','')) = 'govindraj'

[divyakrishnan]

The query is working on Mysql..
My problem is solved now.
Thank you for your help and support.