php mysql查询的第一个字母!=[a-z]

SELECT * FROM dbname WHERE text = 'a%' 
(this can get all the `text` fields which begin with the letter "a".)

But how to get the fields where the first letter !=[a-z]?

但是如何得到第一个字母所在的字段呢?

for example:

例如:

\"hello\"  // this first letter begin as \ not range in a-z
いけ  // also first letter not begin range in a-z
... 
may also have a white space as the first character.

So how can you use php mysql query to get all these kinds of results where the first letter !=[a-z]?

那么，如何使用php mysql查询获得第一个字母!=[a-z]的所有这些结果呢?

4 个解决方案

#1

Try this:

试试这个:

SELECT * FROM dbname WHERE text REGEXP '^[^a-zA-Z]';

That is if you want it to be case insensitive (uppercase or lowercase). If you want to allow uppercase letters A-Z then just use:

这是如果您希望它不区分大小写(大写或小写)。如果你想要大写字母A-Z，那就用:

SELECT * FROM dbname WHERE text REGEXP '^[^a-z]';

Essentially the regular expression says match any string that doesn't have letters a-z at the beginning of the string.

基本上正则表达式表示匹配字符串开头没有字母a-z的字符串。

#2

Use REGEXP/RLIKE:

使用正则表达式/ RLIKE:

SELECT * FROM dbname WHERE text REGEXP '^[^a-z]'

#3

SELECT * FROM dbname WHERE text NOT REGEXP '^[a-z]';

#4

A non-REGEXP solution is to use BETWEEN:

非regexp解决方案是在以下两者之间使用:

SELECT * FROM dbname WHERE LEFT(text, 1) NOT BETWEEN 'a' AND 'Z';

This should be faster than using REGEXP, since MySQL can use indexes on BETWEEN queries, while it cannot on REGEXP queries.

这应该比使用REGEXP要快，因为MySQL可以在查询之间使用索引，而不能在REGEXP查询上使用索引。

However, I have no benchmarks available, should not be to hard to test though.

但是，我没有可用的基准，应该不难测试。

#1