How to eliminate non-english characters using SQL query?

Question

5.00/5 (1 vote)

See more:

How to eliminate non-english characters using SQL query?

What I have tried:

Used the below query but not working.

select * from table where column not like '%[A-Z]%'

Posted 18-May-20 6:28am

Avishek Biswas

Updated 28-May-20 4:05am

Add a Solution

Comments

Richard MacCutchan 18-May-20 12:31pm

What does "not working" mean? What data is in the column field of table, and what results did you receive?

jsc42 18-May-20 12:40pm

As is, the query would find rows where column 'column' does not have any letters in the range A to Z. I guess the OP is looking for rows where column 'column' only contains the letters A through Z, which is a lot trickier. Even if the correct LIKE is used, it wouldn't eliminate anything, it just wouldn't select specific rows to be returned in the query

Avishek Biswas 27-May-20 12:42pm

I have used the below query for the extraction of the data, but it is not working.
Used the below query for the extraction, but it's not giving the correct values.
select * from table where (Replace(ISNULL('(data/data1/data2/)', 'nvarchar(50)'),'') like '%[a-zA-Z0-9]%'

Avishek Biswas 28-May-20 10:06am

i want to eliminate Japanese, thai and Indonesian characters using the query.

For example, the database consists of 5 records, コンサート, sandra, leonardo, 昨夜, 最高, jerry.

Then, in the result, it should return sandra, leonardo, jerry.

Please let me know.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-05-18T06:40:00

Solution 1

Try adding COLLATE SQL_Latin1_General_CP1253_CI_AI to your SELECT:

SQL

SELECT * FROM MyTable WHERE Title LIKE '%e%'
COLLATE SQL_Latin1_General_CP1253_CI_AI

Posted 18-May-20 6:40am

OriginalGriff

Comments

Maciej Los 18-May-20 15:54pm

5ed!