Reqular Expression to trim space on mysql

[divyakrishnan]

Hi...
I am using following query to get data from a table

select * from emp where ename regexp 'rajkiran'

It will return only the rows with ename=rajkiran

but I want to select all the rows with ename =raj kiran & ename=rajkiran using a single query

[pritaeas]

Try this one to match both:

select * from emp where ename regexp 'raj ?kiran'

[divyakrishnan]

Thanks.
How to generalize this query to use with a search engine?

[pritaeas]

How to generalize this query to use with a search engine?

Specify what you mean.

[divyakrishnan]

I have a search engine to search records from mysqldb.

The script used as follows.

$text=$_REQUEST['txt'];
    $q="select * from table where field regexp '$text' ";

I want to get all records from database with and without space character.

For example If $text="KG 1957" or $text="KG1957"
I want all the records containing KG 1957 & KG1957

[pritaeas]

If you are using a fixed format like that: create a new variable from the first two characters. Then add the space and the question mark. Then add the remaining characters. Then use the new variable in your query.

You have to code that business logic, or allow input of a regex in the editbox.

[divyakrishnan]

Search text is not in fixed format.
It varies.
How to change the query to select the data with all possible formats..

[pritaeas]

There is no one regex that can guess your requirements. You can add the ? after every character though.