Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 25th, 2009, 12:38 PM
Authorized User
 
Join Date: Sep 2008
Posts: 54
Thanks: 8
Thanked 1 Time in 1 Post
Default How to get all records from a table, which contains letter 'a' in their names?

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?
__________________
How to do programming?
 
Old March 25th, 2009, 03:16 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.

Last edited by Old Pedant; March 26th, 2009 at 01:15 AM..
 
Old March 25th, 2009, 05:14 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 166
Thanks: 2
Thanked 33 Times in 33 Posts
Default

or to be a bit more standard:

Code:
WHERE name LIKE '%a%,%'
 
Old March 26th, 2009, 01:06 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.

Last edited by Old Pedant; March 26th, 2009 at 01:14 AM..





Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 10:10 AM
Display only names starting with particular letter cookout XSLT 1 June 27th, 2006 11:23 AM
Table names arnabghosh SQL Server 2000 3 November 24th, 2005 10:01 AM
I have records where I need to replace one letter gilgalbiblewheel Classic ASP Databases 2 March 11th, 2005 02:29 AM
How to Get Table Names in ASP using ADO ramk_1978 ADO.NET 2 October 26th, 2004 03:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.