0

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


2
Contributors
9
Replies
10
Views
6 Years
Discussion Span
Last Post by divyakrishnan
0

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)

Edited by peter_budo: Keep It Clear - Do wrap your programming code blocks within [code] ... [/code] tags

0

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.

0

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"

0

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.

0

try this

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

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.