Implementation method:
--function
Create function [dbo]. [Fn_getpy] (@str nvarchar (4000))
Returns nvarchar (4000)
--For encryption
--with encryption
As
Begin
DECLARE @intLen int
declare @strRet nvarchar (4000)
declare @temp nvarchar (100)
Set @intLen = Len (@str)
Set @strRet = ' '
While @intLen > 0
Begin
Set @temp = ' '
Select @temp = case
When substring (@str, @intLen, 1) >= ' as ' Then ' Z '
When substring (@str, @intLen, 1) >= ' ya ' Then ' Y '
When substring (@str, @intLen, 1) >= ' Eve ' then ' X '
When substring (@str, @intLen, 1) >= ' 屲 ' Then ' W '
When substring (@str, @intLen, 1) >= ' he ' then ' T '
When substring (@str, @intLen, 1) >= ' three ' then ' S '
When substring (@str, @intLen, 1) >= ' 呥 ' and then ' R '
When substring (@str, @intLen, 1) >= ' seven ' then ' Q '
When substring (@str, @intLen, 1) >= ' 妑 ' and then ' P '
When substring (@str, @intLen, 1) >= ' Oh ' Then ' O '
When substring (@str, @intLen, 1) >= ' Hallasan ' Then ' N '
When substring (@str, @intLen, 1) >= ' 嘸 ' Then ' M '
When substring (@str, @intLen, 1) >= ' and then ' L '
When substring (@str, @intLen, 1) >= ' ka ' Then ' K '
When substring (@str, @intLen, 1) >= ' not ' then ' J '
When substring (@str, @intLen, 1) >= ' hafnium ' Then ' H '
When substring (@str, @intLen, 1) >= ' Xu ' then ' G '
When substring (@str, @intLen, 1) >= ' send ' then ' F '
When substring (@str, @intLen, 1) >= ' Ehegan ' Then ' E '
When substring (@str, @intLen, 1) >= ' Otah ' Then ' D '
When substring (@str, @intLen, 1) >= ' cha ' Then ' C '
When substring (@str, @intLen, 1) >= ' eight ' then ' B '
When substring (@str, @intLen, 1) >= ' acridine ' Then ' A '
else RTrim (LTrim (substring (@str, @intLen, 1))
End
--no phonetic codes are generated for Chinese character special characters
if (ASCII (@temp) >127) Set @temp = ' '
--For English small brackets, do not generate pinyin code
If @temp = ' (' or @temp = ') ' Set @temp = '
Select @strRet = @temp + @strRet
Set @intLen = @intLen-1
End
return Lower (@strRet)
End
--EXECUTE statement
declare @id int;
DECLARE @UserName varchar (100);
DECLARE @cursor cursor;--Cursors
Set @cursor =cursor for
Select Id,username from T_user;
Open @cursor
FETCH NEXT from @cursor into @id, @UserName;
While @ @FETCH_STATUS =0
Begin
Begin
Update T_user set username= (SELECT [dbo].[ FN_GETPY] (@UserName)) where [email protected]
End
FETCH NEXT from @cursor into @id, @UserName;
End
Close @cursor
Deallocate @cursor
SQL converts all names (kanji) of existing data in the user table to pinyin initials