How to get all records from a table, which contains letter 'a' in their names?

arbab · March 25th, 2009, 12:38 PM

How to get all records from a table "employee", which contains letter 'a' in their last names? this the question in recruitment test i came across with, as far as i know there is no last name and first name,it is a single name like King,John etc
But nevertheless how v can write its query?

Old Pedant · March 25th, 2009, 03:16 PM

IGNORE THIS SILLY ANSWER AND READ PHILIP COLE'S INSTEAD!

Well, it depends on WHICH database you are using.

With MySQL, you could use a regular expression to easily do this:

Code:

SELECT ... WHERE name REGEXP 'a.*?,'  ...
or maybe
SELECT .... WHERE name REGEXP 'a[^,]*,' ...

(says "find an 'a' followed by any number of characters followed by a comma" -- so 'a' has to be in the last name)

In SQL Server, it would be more complex. Something like

Code:

SELECT ...
WHERE CHARINDEX( 'a', name ) > 0
AND CHARINDEX( 'a', name ) < CHARINDEX( ',', name )
...

In Access:

Code:

SELECT ...
WHERE INSTR(name,'a') > 0
AND INSTR(name,'a') < INSTR(name,',')
...

You could, of course, use the CHARINDEX technique with MySQL, too, but the REGEXP would be easier.

philip_cole · March 25th, 2009, 05:14 PM

or to be a bit more standard:

Code:

WHERE name LIKE '%a%,%'

Old Pedant · March 26th, 2009, 01:06 AM

Wasn't sure all DBs would accept that. Clearly, if the DB can handle multiple indefinite wildcards, that's much the better way.

************

Addendum:

Yeah, doofus that I am, I thought it would be too complex for MySQL and Access, at the least.

HAH!

They both handled it just fine.

DOH.