sql中文轉拼音_SQL語句提取出中文的拼音首字母

『壹』 C# 拼音轉中文

首先說明一點，輸入字母或拼音顯示中文不是用C#實現的，而是用資料庫實現的，資料庫中有一列是中文，而還有一列是拼音的簡寫。我剛做過一個系統，用的是VS
2010
+
sql
Server
2008實現的，其中一個功能就是拼音簡寫的模糊查詢，我設計的思想就是在資料庫中設計的有中文的和拼音簡寫的屬性，當然拼音簡寫不是自己手工添加的，是在SQL
SQL
Server
2008建立觸發器實現的。
--SQL中將漢字轉換成拼音，這里是轉換漢字首拼音，如果需要全部轉換，在以下程序中稍微改幾個數字就可以了
--SQL使用
--創建翻譯函數
create
function
Fun_GetPY(@str
nvarchar(4000))
returns
nvarchar(4000)
as
begin
declare
@word
nchar(1),@PY
nvarchar(4000)
set
@PY=''
while
len(@str)>0
begin
set
@word=left(@str,1)
--如果非漢字字元，返回原字元
set
@PY=@PY+(case
when
unicode(@word)
between
19968
and
19968+20901
then
(select
top
1
PY
from
(
select
'A'
as
PY,N'驁'
as
word
union
all
select
'B',N'簿'
union
all
select
'C',N'錯'
union
all
select
'D',N'鵽'
union
all
select
'E',N'樲'
union
all
select
'F',N'鰒'
union
all
select
'G',N'腂'
union
all
select
'H',N'夻'
union
all
select
'J',N'攈'
union
all
select
'K',N'穒'
union
all
select
'L',N'鱳'
union
all
select
'M',N'旀'
union
all
select
'N',N'桛'
union
all
select
'O',N'漚'
union
all
select
'P',N'曝'
union
all
select
'Q',N'囕'
union
all
select
'R',N'鶸'
union
all
select
'S',N'蜶'
union
all
select
'T',N'籜'
union
all
select
'W',N'鶩'
union
all
select
'X',N'鑂'
union
all
select
'Y',N'韻'
union
all
select
'Z',N'咗'
)
T
where
word>=@word
collate
Chinese_PRC_CS_AS_KS_WS
order
by
PY
ASC)
else
@word
end)
set
@str=right(@str,len(@str)-1)
end
return
@PY
end
--函數調用實例：
select
dbo.Fun_GetPY('河南科技大學')
--注意:一定要有dbo
--結果為：HNKJDX
--函數的刪除
drop
function
dbo.Fun_GetPY
--實用案例
--表memberinfo中有欄位(name,password,pinyin),拼音為ID的簡寫
--建立一個觸發器，使表中每增加一行記錄，pinyin中添加ID的簡寫
--注意：一張表中只能建立一個觸發器
create
trigger
addpinyin
on
memberinfo
for
insert
,update
as
declare
@id
varchar(10)
select
@id=name
from
inserted
update
memberinfo
set
pinyin=dbo.Fun_GetPY(@id)
where
name
IN
(select
name
from
inserted)
insert
into
memberinfo(ID,password)values('河南科技','471003')
select
*
from
memberinfo

『貳』怎麼在SQL SERVER中把漢字轉化為拼音碼

CREATE FUNCTION Fun_GetPY
(
@Str NVARCHAR(4000)
)

RETURNS NVARCHAR(4000)

AS

BEGIN
DECLARE @Word NCHAR(1)
DECLARE @PY NVARCHAR(4000)

SET @PY = ''

WHILE LEN(@Str) > 0
BEGIN
SET @Word = LEFT(@Str, 1)

--如果非漢字字元，返回原字元
SET @PY = @PY + (CASE WHEN UNICODE(@Word) BETWEEN 19968 AND 19968 + 20901
THEN (SELECT TOP 1 PY FROM(
SELECT 'A' AS PY, N'驁' AS WORD
UNION ALL SELECT 'B',N'簿'
UNION ALL SELECT 'C',N'錯'
UNION ALL SELECT 'D',N'鵽'
UNION ALL SELECT 'E',N'樲'
UNION ALL SELECT 'F',N'鰒'
UNION ALL SELECT 'G',N'腂'
UNION ALL SELECT 'H',N'夻'
UNION ALL SELECT 'J',N'攈'
UNION ALL SELECT 'K',N'穒'
UNION ALL SELECT 'L',N'鱳'
UNION ALL SELECT 'M',N'旀'
UNION ALL SELECT 'N',N'桛'
UNION ALL SELECT 'O',N'漚'
UNION ALL SELECT 'P',N'曝'
UNION ALL SELECT 'Q',N'囕'
UNION ALL SELECT 'R',N'鶸'
UNION ALL SELECT 'S',N'蜶'
UNION ALL SELECT 'T',N'籜'
UNION ALL SELECT 'W',N'鶩'
UNION ALL SELECT 'X',N'鑂'
UNION ALL SELECT 'Y',N'韻'
UNION ALL SELECT 'Z',N'咗') T
WHERE WORD >= @Word COLLATE Chinese_PRC_CS_AS_KS_WS
ORDER BY PY ASC)
ELSE @Word
END)
SET @Str = RIGHT(@Str, LEN(@Str) - 1)
END
RETURN @PY
END

調用這個函數就可以

『叄』怎樣寫SQL存儲過程漢字轉換拼音簡碼sqlserver2000

使用SQL Server Management Studio在列表中查看即可。步驟： 1、登錄SQL Server Management Studio。 2、左邊的樹點擊要查詢的庫左邊的「+」，點開後會變成「-」，下同。 3、點擊可編程性-存儲過程

『肆』 sql批量生成拼音碼問題

1
創建這個函數。
Create

function fun_getPY
(
@str
nvarchar(4000)
)

returns

nvarchar(4000)
as

begin

declare

@word nchar(1),@PY nvarchar(4000)
set

@PY=''
while

len(@str)>0
begin

set

@word=left(@str,1)
--
set

@PY = @PY+(casewhenunicode(@word)between 19968 and 19968+20901

then (
select

top 1 PY
from

(

select

'A'as PY,N''as word
union

allselect'B',N''
union

allselect'C',N''
union

allselect'D',N''
union

allselect'E',N''
union

allselect'F',N''
union

allselect'G',N''
union

allselect'H',N''
union

allselect'J',N''
union

allselect'K',N''
union

allselect'L',N''
union

allselect'M',N''
union

allselect'N',N''
union

allselect'O',N''
union

allselect'P',N''
union

allselect'Q',N''
union

allselect'R',N''
union

allselect'S',N''
union

allselect'T',N''
union

allselect'W',N''
union

allselect'X',N''
union

allselect'Y',N''
union

allselect'Z',N''
)

T
where

word>=@word collate Chinese_PRC_CS_AS_KS_WS
order

by PY ASC
)

else

@word
end

)
set

@str=right(@str,len(@str)-1)
end

return

@PY
end

2
select dbo.fun_getPY(該欄位) from 表

『伍』如何在sql 中將表中一整列的漢字全部變成拼音首字母

『陸』資料庫oracle（plsql）怎麼把漢字轉化成拼音首字母,如果非漢字字元（英文or數字），返回原字元不變

oracle漢字轉拼音(獲得全拼/拼音首字母/拼音截取等)效果如下: Oracle 字元集 GBK 沒有問題 , UTF -8 需要修改一下Sql代碼
--oracle漢字轉拼音 PACKAGE
--1.獲得全拼
SELECT GETHZPY.GETHZFULLPY('漢字') FROM DUAL;結果 : HanZi
--2.拼音首字母
SELECT GETHZPY.GETHZPYCAP('漢字') FROM DUAL;結果 : HZ
--3.拼音截取等
SELECT GETHZPY.GETHZPYCAPSUBSTR('漢字', 0, 1) FROM DUAL;結果 : H
代碼部分太長掛在附件上以下代碼如果在 PL/SQL Developer 執行的話,選擇 Command Window 粘貼.
附件在最下面.
oracle漢字轉拼音package_獲得全拼——拼音首字母_拼音截取等.zip (35.9 KB)

『柒』 SQL語句把用戶名中文改成拼音字母

看看這個http://dhjdhja.blog.163.com/blog/static/64762009101684639346/ ps：奶奶的貼一個地址鏈接還不行什麼破論壇，內容又發不完 --sql漢字轉拼音
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOcreate function [dbo].[fn_GetPinyin](@words nvarchar(2000))
returns varchar(8000)
as
begin
declare @word nchar(1)
declare @pinyin varchar(8000)
declare @i int
declare @words_len int
declare @unicode int
set @i = 1
set @words = ltrim(rtrim(@words))
set @words_len = len(@words)
while (@i <= @words_len) --循環取字元
begin
set @word = substring(@words, @i, 1)
set @unicode = unicode(@word)
set @pinyin = ISNULL(@pinyin+space(1),'')+
(case when unicode(@word) between 19968 and 19968+20901 then
(select top 1 py from (
select 'a' as py,N'厑' as word
union all select 'ai',N'靉'
union all select 'an',N'黯'
union all select 'ang',N'醠'
union all select 'ao',N'驁'
union all select 'ba',N'欛'
union all select '',N'瓸' --韛兡瓸
union all select 'ban',N'瓣'
union all select 'bang',N'鎊'
union all select 'bao',N'鑤'
union all select 'bei',N'鐾'
union all select 'ben',N'輽'
union all select 'beng',N'鏰'
union all select 'bi',N'鼊'
union all select 'bian',N'變'
union all select 'biao',N'鰾'
union all select 'bie',N'彆'
union all select 'bin',N'鬢'
union all select 'bing',N'靐'
union all select 'bo',N'卜'
union all select 'bu',N'簿'
union all select 'ca',N'囃'
union all select 'cai',N'乲' --縩乲
union all select 'can',N'爘'
union all select 'cang',N'賶'
union all select 'cao',N'鼜'
union all select 'ce',N'簎'
union all select 'cen',N'笒'
union all select 'ceng',N'乽' --硛硳岾猠乽
union all select 'cha',N'詫'
union all select 'chai',N'囆'
union all select 'chan',N'顫'
union all select 'chang',N'韔'
union all select 'chao',N'觘'
union all select 'che',N'爡'
union all select 'chen',N'讖'
union all select 'cheng',N'秤'
union all select 'chi',N'鷘'
union all select 'chong',N'銃'
union all select 'chou',N'殠'
union all select 'chu',N'矗'
union all select 'chuai',N'踹'
union all select 'chuan',N'鶨'
union all select 'chuang',N'愴'
union all select 'chui',N'顀'
union all select 'chun',N'蠢'
union all select 'chuo',N'縒'
union all select 'ci',N'嗭' --賜嗭
union all select 'cong',N'謥'
union all select 'cou',N'輳'
union all select 'cu',N'顣'
union all select 'cuan',N'爨'
union all select 'cui',N'臎'
union all select 'cun',N'籿'
union all select 'cuo',N'錯'
union all select 'da',N'橽'
union all select 'dai',N'靆'
union all select 'dan',N'饏'
union all select 'dang',N'闣'
union all select '',N'纛'
union all select 'de',N'的'
union all select 'den',N'扽'
union all select 'deng',N'鐙'
union all select 'di',N'螮'
union all select 'dia',N'嗲'
union all select 'dian',N'驔'
union all select 'diao',N'鑃'
union all select 'die',N'嚸' --眰嚸
union all select 'ding',N'顁'
union all select 'diu',N'銩'
union all select 'dong',N'霘'
union all select 'dou',N'鬭'
union all select '',N'蠹'
union all select 'an',N'叾' --籪叾
union all select 'i',N'譵'
union all select 'n',N'踲'
union all select 'o',N'鵽'
union all select 'e',N'鱷'
union all select 'en',N'摁'
union all select 'eng',N'鞥'
union all select 'er',N'樲'
union all select 'fa',N'發'
union all select 'fan',N'瀪'
union all select 'fang',N'放'
union all select 'fei',N'靅'
union all select 'fen',N'鱝'
union all select 'feng',N'覅'
union all select 'fo',N'梻'
union all select 'fou',N'鴀'
union all select 'fu',N'猤' --鰒猤
union all select 'ga',N'魀'
union all select 'gai',N'瓂'
union all select 'gan',N'灨'
union all select 'gang',N'戇'
union all select 'gao',N'鋯'
union all select 'ge',N'獦'
union all select 'gei',N'給'
union all select 'gen',N'搄'
union all select 'geng',N'堩' --亘堩啹喼嗰
union all select 'gong',N'兣' --熕贑兝兣
union all select 'gou',N'購'
union all select 'gu',N'顧'
union all select 'gua',N'詿'
union all select 'guai',N'恠'
union all select 'guan',N'鱹'
union all select 'guang',N'撗'
union all select 'gui',N'鱥'
union all select 'gun',N'謴'
union all select 'guo',N'腂'
union all select 'ha',N'哈'
union all select 'hai',N'饚'

『捌』 php mysql 如何將中文轉換拼音、小寫轉大寫；並排序

首先在網上找找函數把中文轉換成拼音的
例如 to_pinyin();
然後
$data_sort = array();
foreach($data as $row){
$data[to_pinyin($row)] = $row;
}
最後用鍵值排序就可以了

『玖』 SQL語句提取出中文的拼音首字母

正好最近收藏了一個你可以看下思路
--將中文字元串轉化成文字首拼音的組合
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非漢字字元，返回原字元
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
--函數調用實例：
select dbo.fun_getPY('中華人民共和國AAA01')

/*

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZHRMGHGAAA01

(1 行受影響)

*/

sql中文轉拼音

與sql中文轉拼音相關的內容