CODEWITHSUNDEEP
sqlsql-server
JimmyPop13
JimmyPop13

Reputation: 317

Selecting special characters only from table with Japanese name

I'm using SQL Server on a table of Japanese names. I'm trying to return any field that contains a special character(eg, - / $ .) but it's also returning any Japanese character. 

select names from nameTable
where 
(names <> '' and names not like N'%[a-z0-9]%')

I'm using various versions of the above to get my results and it does technical work.

Just wondering if anyone has any advice on how to exclude the Japanese characters from my results.

If more information is needed please ask.

Upvotes: 0

Views: 983

Answers (2)

Zack
Zack

Reputation: 2341

Your query isn't working because you're specifying ASCII characters in your LIKE clause. So any non-ASCII characters (including Japanese characters) will show up in your result.

There may well be a better answer (including @denial's suggestion to create a CLR function). But another possible solution is to hard-code the special characters you need. Something like this:

select * from JapaneseText
where 
    chars like N'%[[!"#$%&''()*+`-./:;<=>?@\^_]%'
    -- separate check for ']', to avoid it 
    -- being interpreted as the end of the character class
    or chars like N'%]%'

Of course, these are just the special characters from the ASCII table. You'd have to tweak the solution to handle more characters (smart quotes, dashes, etc.) But then it would really start to get unwieldy, so you might very well be better off approaching it from another angle.

Upvotes: 1

denial
denial

Reputation: 1

It comes in mind that you should use CLR UDF's for that purpose. Seems to me that you couldn't achieve this with LIKE.
That is you could make an assembly, eg. written in C# which could return regex matches from a given column. More on CLR UDF's here: https://www.red-gate.com/simple-talk/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

Upvotes: 0

Related Questions