Paul Paul - 16 days ago 5
SQL Question

How to detect whether column NVARCHAR contains latin or cyrilic chars

I have "Template" table:

CREATE TABLE Template (
ID BIGINT, -- PK
NAME NVARCHAR(255)
)


Column NAME contains russian or english text. How can I move value of this column to RUSSIAN_NAME and ENGLISH_NAME columns in depends on value of NAME column value.

CREATE TABLE Template (
ID BIGINT, -- PK
RUSSIAN_NAME NVARCHAR(255),
ENGLISH_NAME NVARCHAR(255)
)

Answer

Try this:

I have no idea what the russian text is meaning, just copied from somewhere

DECLARE @tbl TABLE (name NVARCHAR(255),plainLatin NVARCHAR(255),foreignChars NVARCHAR(100));
INSERT INTO @tbl(name) VALUES
 (N'abcd'),(N'слов в тексте'),(N'one more'),(N'с пробелами и без них');

UPDATE @tbl
SET plainLatin=CASE WHEN PATINDEX('%[^-a-zA-Z0-9 ]%' /*add signs you want to allow*/,name)=0 THEN name END
   ,foreignChars=CASE WHEN PATINDEX('%[^-a-zA-Z0-9 ]%' /*add signs you want to allow*/,name)>0 THEN name END

SELECT * FROM @tbl

The result

+-----------------------+------------+-----------------------+
| name                  | plainLatin | foreignChars          |
+-----------------------+------------+-----------------------+
| abcd                  | abcd       | NULL                  |
+-----------------------+------------+-----------------------+
| слов в тексте         | NULL       | слов в тексте         |
+-----------------------+------------+-----------------------+
| one more              | one more   | NULL                  |
+-----------------------+------------+-----------------------+
| с пробелами и без них | NULL       | с пробелами и без них |
+-----------------------+------------+-----------------------+