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中文转拼音相关的内容