I have the following SQL statement that retrieves the distinct first letter for each entry in the database. I'm using this to build a alphabetized list for a glossary.

PHP Code:

SELECT DISTINCT LEFT(art_title,1) AS first_letter FROM articles ORDER BY first_letter ASC 


The problem I'm having is when the first letter in the string is a quote, single quote or some other character.

Is there a way in the SQL statement to replace any unwanted characters.

Cheers

Wayne.

Using what database server?

considering that this is the PHP forum, SQL is NOT PHP and PHP can "talk" to several SQL Servers.

MySQL. Apologies for posting in the wrong forum. Could you move it to the appropriate forum.

Regular Expressions.

Use a pattern of ^[a-zA-Z] to match lines that start letters only, ^[0-9] to match numerics and "NOT ^[a-zA-Z0-9]"

The ^ means "Starts with" so you don't need the string slicing.

Thanks Chris,

This seems to work.

PHP Code:

SELECT DISTINCT LEFT(art_title,1) AS first_letter FROM articles WHERE art_title REGEXP '^[a-zA-Z]' ORDER BY first_letter ASC 


Out of interest, if there was 1 article titled "Zulu............." including the quotes, the letter Z would not be in the results as it doesn't start with a letter.

Can regular expressions disregard the first character if it's not a letter? so that the Z will appear in the results?

Cheers

Wayne.

to match a quote mark followed by a character the pattern would be

^[\'\"]+[A-Za-z0-9]

that says "a string that starts with ' or " followed by an alphanumeric character.

Cheers Chris