Return rows starting with an alpha, ignoring punctuation and other non alpha characters

Asked Oct 29 '14 at 14:12

Active Oct 29 '14 at 14:12

Viewed 86 times

I have a relatively simple SQL query which I am using to return the first character from all rows in a table (grouped by that character) in order to create a filter where users can pick 'by surname'. I have some bad data in the table where non alpha characters have been used, i.e. full stops, commas and brackets and I would like to ignore these in the query.

SELECT UPPER(SUBSTRING(field_id_35, 1, 1)) as surname
FROM exp_channel_data
WHERE channel_id=8 AND field_id_35<>''
GROUP BY surname order by surname

How would I go about doing that? I have tried using REGEXP but without success. Any pointers greatly appreciated. :)

asked Oct 29 '14 at 14:12

Nathan Pitman

2,286
4
30
46

Alas, mysql does not support regex-based replacement, which would solve your problem. – Bohemian Oct 29 '14 at 14:15
This might help http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql – Daniel Gimenez Oct 29 '14 at 14:20
Thanks both. In the end I gave in and did some post query processing to solve this. I feel dirty but at least it works. – Nathan Pitman Nov 17 '14 at 15:17

Return rows starting with an alpha, ignoring punctuation and other non alpha characters

0 Answers0