SQL Server removes the Chinese in the field

Very often, some fields in a database table are made up of Chinese and letters or numbers, but sometimes we need to remove the Chinese from the field. There are a number of ways to implement this requirement, and here's one solution.

First we set up the test data

CREATE TABLE test (ID int primary key identity (), name varchar () not NULL) INSERT INTO Test (' Test 2 ') the insert in To test (name) values (' Test a ') insert into test (name) VALUES (' test ') insert INTO Test (' abc ') insert into test (name) VALUES (' 123 ') insert into test (name) VALUES (' abc123 ') SELECT * FROM Test

Results:

To create a function:

--Remove the Chinese create function Fun_del_chinese (@col varchar) returns varchar (asbegin) DECLARE @ in the input string    Returnchar varchar, @len int    Select @returnchar = ", @len =1 while     (@len <=len (@col))    begin        If (ASCII (substring (@col, @len, 1)) <122)        Set @[email protected]+substring (@col, @len, 1)        set @[email protected]+1    Endreturn @returncharendgo

Perform:

Update test set Name=t2.namefrom test T1, (select Id,dbo.fun_del_chinese (name) name from Testwhere Len (name) *2!= DATALENGTH (name)-Excludes all Chinese-made fields) T2where T1.id=t2.id

Execution Result:

SQL Server removes the Chinese in the field