mysql regex get position of matched first alphabetic character

Question

I have a mysql query with REGEXP which match the starting of field with 'A', 'An' and 'The' Followed by space if match then trim the field from starting of first space, then i match the starting of field with special character like (','',[:space:]) if yes then trim all the leading special character. Mysql query is with CASE like this:

CASE
  WHEN field_data_field_display_title_field_display_title_value REGEXP '(^(A|An|The)[[:space:]])' = 1 THEN
  TRIM(SUBSTR(field_data_field_display_title_field_display_title_value , INSTR(field_data_field_display_title_field_display_title_value ,' ')))
  WHEN field_data_field_display_title_field_display_title_value REGEXP '(^[\"\'[:space:]])' = 1 THEN
    TRIM(SUBSTR(field_data_field_display_title_field_display_title_value ,2))
  ELSE field_data_field_display_title_field_display_title_value
END

I am not able to trim all leading special character while i can trim the first leading special character by passing '2' in SUBSTR function. As mysql doesn't support capturing group so i can't get the matched value in captured group.

So my question is how can i get the position of first alphabetic character in field with mysql query so that i can pass that position in SUBSTR function to trim all the leading special character. I tried with [:alpha:] class like:

TRIM(SUBSTR(field_data_field_display_title_field_display_title_value ,
 INSTR(field_data_field_display_title_field_display_title_value ,[:alpha:])))

but it give mysql syntax error. Or Anybody can suggest me any other approach to trim all the leading special characters.

Thanks in Advance!

Answer 1

I was using the mysql snippet i posted in question in ORDER BY clause to sort the data. As i was having small list of matches which i want to remove so i followed @BillKarwin suggestion. ORDER BY clause in query become something like

ORDER BY 
  CASE
    WHEN field_data_field_display_title_field_display_title_value REGEXP '^(A|An|The)[[:space:]]' = 1 THEN
      TRIM(SUBSTR(field_data_field_display_title_field_display_title_value , INSTR(field_data_field_display_title_field_display_title_value ,' ')))
    WHEN field_data_field_display_title_field_display_title_value REGEXP '^[\']' = 1 THEN
      TRIM(LEADING '\'' FROM field_data_field_display_title_field_display_title_value)
    WHEN field_data_field_display_title_field_display_title_value REGEXP '^[[:space:]]' = 1 THEN
      TRIM(LEADING ' ' FROM field_data_field_display_title_field_display_title_value)
    WHEN field_data_field_display_title_field_display_title_value REGEXP '^[\"]' = 1 THEN
      TRIM(LEADING '"' FROM field_data_field_display_title_field_display_title_value)
    ELSE field_data_field_display_title_field_display_title_value
  END ASC