CODEWITHSUNDEEP
sqlsql-server
Paul
Paul

Reputation: 594

How to detect whether column NVARCHAR contains Latin or Cyrillic 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)
)

Upvotes: 0

Views: 239

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

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       | с пробелами и без них |
+-----------------------+------------+-----------------------+

Upvotes: 2

Related Questions