Hello,

I am trying to write a sql statement that will select the first letter of the last name. The name, unfortunately is all run together with no rhyme or reason....ie) john doe or john h doe or john henry doe.

$sql="SELECT * from webpagetable WHERE (first letter of last name) = m OR M";

Can anyone offer any guidance or place on net for code?

Regards,
Matt

i'd say the best approach would be having a separate field for last mane and checking with m% or M% ... i can't think of any statement for that right now.

you could use php to find the last space in the name and return the next character (firt letter of last name) but i think you would be better off redesigning the db structure.

just an opinion.

Is the second name stored in a seperate cell to the first name, or is the whole name in one cell?

Simple solution...

PHP Code:

// You can add a WHERE clause, or change the selection depending on what you need.
    $sql = "SELECT last_name from webpagetable";
$query = mysql_query($sql);
$names = mysql_fetch_array($query, MYSQL_NUM)

foreach ($names as $key => $val){
    $segments = explode(' ', $val);
    $values = count($segments)-1;
    $last_initial = ucwords(substr($segments[$values], 0,1));
} 


the name is all in one cell

Use SUBSTRING() to get the first letter, I think.

SELECT * FROM webpagetable WHERE SUBSTRING(lastname, 0, 1) = 'm' OR SUBSTRING(lastname, 0, 1) = 'M'

Logical Program, that's a solution I wouldn't want to use. You would be reading all rows into memory, then looping through all of them. If you had lots of rows, it would take an enormous amount of resources and take a lot of time to complete.

// Edit
Oh, the name is all in one field. Then I think you might be able to use SUBSTRING_INDEX combined with SUBSTRING. You can try it

I think using regex would be good in this situation. I don't have time to test out an expression right now, though. Hopefully that'll help at least a little.

you could also use a simple match like :

select * from TABLE where last_name like 'A%'

which would also return all entries where last name starts with a A.

ibbo