0

The table "article" has a cloumn named "A_Title". I want to select the A_Title that matches exactly both /[A-Za-z]+/ and /[\x{0981}-\x{09E3}]/u regex at the same time.

I tried several methods for it, but neither is working. Giving error: #1139 - Got error 'invalid character range' from regexp

SELECT A_Title FROM article WHERE A_Title REGEXP '[A-Za-z]+.*[\x{0981}-\x{09E3}]|[\x{0981}-\x{09E3}].*[A-Za-z]+'

SELECT A_Title FROM article WHERE A_Title REGEXP '[A-Za-z]+.*[\\x{0981}-\\x{09E3}]|[\\x{0981}-\\x{09E3}].*[A-Za-z]+'

Below code works, but it is not using the same regex:

SELECT A_Title FROM article WHERE A_Title RLIKE '[A-Za-z]+.*[ঀ-৳]|[ঀ-৳].*[A-Za-z]+'
Tushar
  • 3,527
  • 9
  • 27
  • 49
Rains
  • 29
  • 4
  • 1
    @Tushar #1139 - Got error 'invalid character range' from regexp – Rains Mar 05 '23 at 09:34
  • @Tushar Doesn't work, I already have this SQL statement in my question mentioning - "Not working" – Rains Mar 05 '23 at 09:36
  • Hi @Rains, could you specify what DB engine you are testing on? special character could be a bit dffierent in different DB engines. – Junjie Mar 05 '23 at 09:36
  • 1
    I'm testing on the MySQL engine – Rains Mar 05 '23 at 09:42
  • Thanks for updating the DB engine. In Mysql; The following shall work : `SELECT A_Title FROM article WHERE A_Title REGEXP '[A-Za-z]+.*[\\u0981-\\u09E3]|[\\u0981-\\u09E3].*[A-Za-z]+';` Can you tell me if you still face any issues ? – Tushar Mar 05 '23 at 09:44
  • @Tushar no it does not. I'm requesting you to read my question again. – Rains Mar 05 '23 at 09:46
  • @Tushar I want both REGEX at the same. – Rains Mar 05 '23 at 09:47
  • Yes; I read it again. I also understand from your working regexp that you are using this for comparing `Bangla` characters. For MySQL regex; `\x{0981}` this syntax is incorrect; So, I just removed the `x` from it in my above solution. Can you use it as : `SELECT A_Title FROM article WHERE A_Title REGEXP '[A-Za-z]+.*[\u0981-\u09E3]|[\u0981-\u09E3].*[A-Za-z]+';` and let me know – Tushar Mar 05 '23 at 09:51
  • "exactly both" ??? Please provide a few samples that should match _and a few that should _not_ match. – Rick James Mar 28 '23 at 21:25

1 Answers1

0

For MySQL regex; \x{0981} this syntax is incorrect; So, just remove the x from it in your regexp.

and then you can combine these both REGEXP statements the same way you have written your working regexp in the post :

SELECT A_Title FROM article WHERE A_Title REGEXP '[A-Za-z]+.*[\\u0981-\\u09E3]|[\\u0981-\\u09E3].*[A-Za-z]+';

As you have already tagged RLIKE in your post; you can even use it instead of REGEXP as REGEXP and RLIKE are synonyms for REGEXP_LIKE() synonym of REGEXP.

Tushar
  • 3,527
  • 9
  • 27
  • 49