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..


what is the query that you are working on.

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)

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.

I am getting the error like

FUNCTION employ.translate does not exist

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"

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.

Can I solve this using REGEXP?

try this

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.