Converting Chinese characters into full spelling in SQL

This must be the. Top of the pinyin library.

Zjcxc (zhujian) on the second floor replied to 19:54:20 score 95
/* -- Get Chinese pinyin from all pinyin

First, use the inverse Conversion Function of the input generator (imegen.exe ).
Convert the complete code table file winpy. MB into a text file c: \ winpy.txt
Use the following statement to import the data to the database.
The following only shows the process of reading and displaying, and is not stored in a specific table.

In the read Statement:
With (datafiletype = 'widechar ') is used to process Unicode data
I tested it in Win2000, And the converted text file encoding is Unicode.
If the encoding is ANSI, do not use this sentence.

View the text file encoding. You can use NotePad to open the text file, save it as, and you will see the current encoding.
--*/

-- Create a temporary table
Create Table # T (A varchar (500 ))

-- Import data
Bulk insert # T from 'C: \ winpy.txt'
With (datafiletype = 'widechar ')

-- Delete the header
Set rowcount 12
Delete from # T
Set rowcount 0

-- Split Processing
Select Chinese Character = left (A, patindex ('% [A-Z] %', a)-1)
, Pinyin = stuff (A, 1, patindex ('% [A-Z] %', a)-1 ,'')
From # T
-- Where patindex ('% [A-Z] %', a) = 2 -- Obtain the pronunciation of a single word

-- Delete test
Drop table # ttop

Zjcxc (zhujian) on the third floor replied to 19:56:16, 2006-01, with a score of 0.
-- Example of using the pinyin database to process pinyin


/* -- Obtain the function of Chinese pinyin

You need to create a pinyin table that contains the pronunciation of all Chinese characters.
Convert the encoding library of the all-input method. Here is only a simple example.
-- Producer build 2003.10 --*/

-- Create a Chinese pinyin Database
Create Table yingshe (CHR char (2), Py varchar (10 ))
Insert yingshe
Select 'length', 'Chang'
Union all select 'length', 'zhang'
Union all select 'city', 'cheng'
Union all select 'core', 'kel'
Union all select 'tech', 'ji'
Union all select 'King', 'jin'
Union all select 'lil', 'lil'
Union all select 'Chapter ', 'zhang'
Union all select 'gong ', 'gong'
Union all select 'ss', 'si'

/* -- The following are two functions. One function returns all the pinyin characters of a string in the form of a table, and the other function returns one of the pinyin characters of a string.
--*/

Go
-- Get Chinese pinyin function -- return all Chinese pinyin
Create Function f_getpy_tb (@ STR varchar (100 ))
Returns @ TB table (revarchar (8000 ))
As
Begin
Declare @ Re table (ID int, re varchar (8000) -- intermediate data processing table

Declare @ I int, @ ilen int, @ splitchr varchar (1)
Select @ splitchr = ''-- delimiter between two pinyin characters (for general purpose)
, @ I = 1, @ ilen = Len (@ Str)

Insert into @ Re select @ I, Py from yingshe where CHR = substring (@ STR, @ I, 1)
While @ I <@ ilen
Begin
Set @ I = @ I + 1
Insert into @ Re select @ I, RE + @ splitchr + py from @ Re A, yingshe B
Where a. ID = @ I-1 and B. CHR = substring (@ STR, @ I, 1)
End

Insert into @ TB select re from @ Re where id = @ I
Return
End
Go

-- Get Chinese pinyin function -- return a Chinese pinyin
Create Function f_getpy (@ STR varchar (100 ))
Returns varchar (8000)
As
Begin
Declare @ Re varchar (8000)

Declare @ I int, @ ilen int, @ splitchr varchar (1)
Select @ splitchr = ''-- delimiter between two pinyin characters (for general purpose)
, @ I = 1, @ ilen = Len (@ Str)

Select @ Re = py from yingshe where CHR = substring (@ STR, @ I, 1)
While @ I <@ ilen
Begin
Set @ I = @ I + 1
Select top 1 @ Re = @ Re + @ splitchr + py
From yingshe where CHR = substring (@ STR, @ I, 1)
End

Return (@ Re)
End
Go

-- Test
-- Return all possible Pinyin of 'Great Wall'
Select * From DBO. f_getpy_tb ('Great Wall ')

-- Application case:

Create Table mytable (name varchar (10), Id INT)
Insert mytable select 'Great Wall tech ', 1
Union all select 'Great Wall Science and Technology ', 2
Union all select 'zhangzhang City company ', 3

Go

-- Query records that contain the same pinyin format as the 'Great Wall:
Select * From mytable
Where exists (select 1 from DBO. f_getpy_tb ('Great Wall ') Where DBO. f_getpy (name) like' % '+ RE +' % ')

-- Delete a table
Drop table yingshe, mytable
-- Delete pinyin Functions
Drop function f_getpy, f_getpy_tb

Original article: http://topic.csdn.net/t/20060601/19/4794741.html

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/zhou__zhou/archive/2007/12/10/1927679.aspx