Pinyin code query is automatically implemented using Stored Procedures + triggers

Steps:

1. Download Chinese pinyin table: http://download.csdn.net/source/326464

2. Put the downloaded T-SQL statement into the database that requires functionality to run. will generatePycode This table

3. Create a field in the table to be implemented and name itNamepy

4. Run the following stored procedures and triggers:

<1> A stored procedure is used to read and query each Chinese character in a field in its pinyin code (No changes required)

<2> trigger: When a Chinese field is inserted or updated, the trigger automatically inserts or changes the pinyin code.

Code

  --  The stored procedure converts Chinese characters into pinyin output  
 Create     Proc  Hz2py
  @ Hz     Varchar  (  200  ),
  @ Py     Varchar  (  200  ) Output
  As 
  Declare     @ I     Int  
  Declare     @ Chr     Varchar  (  2  )
  Declare    @ PyC     Varchar  (  1  )
  Set     @ I  =  1  
  While  (  @ I  <=  Len (  @ Hz  ))  Begin  
  Set     @ Chr  =  Substring  (  @ Hz  ,  @ I  ,  1  )
  Set    @ PyC  =  (  Select  PY  From  Pycode  Where  Hz  =  @ Chr  )
  If  (  @ PyC     Is    Null  )
  Set     @ Py  =  @ Py  +  @ Chr  
  Else  
  Set     @ Py  =  @ Py +  @ PyC  
  Set     @ I  =  @ I  +  1  
  End  

  --  The pinyin code is changed when the trigger is inserted and updated.  -- Namepy is a pinyin field. -- Bank indicates the name of the table to be implemented.  -- Customername is a Chinese character field  -- Cardid is the primary key of the bank table.  
  Create     Trigger  Namepy  On  Bank  For     Insert  ,  Update  
 As  
  Declare     @ Name     Varchar  (  100  ),  @ YY     Varchar  (  100  ),  @ CID    Char  (  10  )

  If  (  Update  (Customername ))  Begin  
  Set     @ YY  =  ''  
 Select     @ Name  =  Customername,  @ CID  =  Cardid  From  Inserted  -- Call a stored procedure
  Exec  Hz2py  @ Name  ,  @ YY Output

  Update  Bank  Set  Namepy  =  @ YY     Where  Cardid  =  @ CID  

  End  
  Go 

 

Well, if you have any questions, leave a message to me ..