Get the first letter of a surname?

[Mr Chris]

Hi,

 

Say I have a name in a database field called 'Name' and it contains the following Values:

 

John Smith

Mark Jones

Jake Smyth

Fred Jakes

Mark Masey

Andy Abraham

 

How could I run a query to output the surnames starting with the letter 'A' or 'B' then 'C' etc... despite them being in one field called 'name' with each persons full name in it?

 

Thanks

 

Thanks

 

[cooldude832]

There is a tutorial out there on how you should run names using an index but basically its a complex question because a simple substr() doesn't work because of variable first name lengths.

 

http://www.sitepoint.com/article/optimizing-mysql-application/2  that shows you a way to accomplish this easily using 2 fields + an index.

 

 

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

The above link is all string functions see if one helps you

[Barand]

try, this (first character after a space in the name)

SELECT name, SUBSTRING(pupil_name, LOCATE(' ', pupil_name)+1, 1) as initial FROM tablename