mysql正则表达式获取匹配的第一个字母字符的位置 [英] mysql regex get position of matched first alphabetic character

问题描述

我有一个带有 REGEXP 的 mysql 查询，该查询将字段的开头与 'A'、'An' 和 'The' 匹配，如果匹配，则后跟空格，然后从第一个空格的开头修剪字段，然后匹配字段的开头带有特殊字符，如 (','',[:space:]) 如果是，则修剪所有前导特殊字符.Mysql 查询使用 CASE 如下:

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

我无法修剪所有前导特殊字符，而我可以通过在 SUBSTR 函数中传递 '2' 来修剪第一个前导特殊字符.由于mysql不支持捕获组，所以我无法在捕获组中获取匹配的值.

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.

所以我的问题是如何使用 mysql 查询获取字段中第一个字母字符的位置，以便我可以在 SUBSTR 函数中传递该位置以修剪所有前导特殊字符.我尝试使用 [:alpha:] 类:

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:])))

但它给了mysql语法错误.或者任何人都可以建议我使用任何其他方法来修剪所有主要的特殊字符.

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

提前致谢！

推荐答案

我使用了我在 ORDER BY 子句中发布的有问题的 mysql 代码段来对数据进行排序.因为我有一小部分我想删除的匹配项，所以我遵循了@BillKarwin 的建议.查询中的 ORDER BY 子句变成类似

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

这篇关于mysql正则表达式获取匹配的第一个字母字符的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！