CODEWITHSUNDEEP
mysqlsqlselect
Saba
Saba

Reputation: 85

MySQL - query to get all rows that a specific character is non-English

I have a table that has nvarchar elements. This table has two kinds of elements:

  1. elements with only digit characters
  2. elements with digit characters and the 3rd character is non-English character

I want a query to get all rows that their 3rd character is non-English.

EDIT

use WHERE SUBSTRING(<table>.ColumnName, 3, 1) NOT BETWEEN '0' AND '9' worked for me either

Upvotes: 2

Views: 826

Answers (2)

Jeffy Mathew
Jeffy Mathew

Reputation: 580

You can use RLIKE operator, below is the query for matching the third character which is not a digit and not an English alphabet

SELECT * FROM 
mytable 
where SUBSTR(mycol,3,1) NOT RLIKE '^[A-Za-z0-9]$';

Upvotes: 0

Mureinik
Mureinik

Reputation: 312289

I'd use regexp_like with a regex that the third character isn't a digit:

SELECT *
FROM   mytable
WHERE  REGEXP_LIKE(mycol, '..[^[:digit:]].*')

In MySQL versions older than 8.0, you could use the regexp operator:

SELECT *
FROM   mytable
WHERE  mycol REGEXP '..[^[:digit:]].*'

Upvotes: 1

Related Questions