Ⅰ 数据库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'饚'
Ⅲ sql 汉字转拼音，若有重复的在拼音加1
题主意思，表a已经有字段hz，现在要更新py这列？这样的话，只要update就行了，为什么说最好是函数实现呢？还是说题主想要selecthz,func(hz)froma这样的呢？
函数。
首先需要一个解析汉字到拼音的方法，这里有一个包，你可以复制过去直接执行。
脚本地址：
http://www.cnblogs.com/mellowsmile/p/4601288.html
附代码：
--不是我不贴，代码太多贴不上，题主还是去上面那个地址复制吧，注意复制全部。
在SQL窗口执行。
我这里建了一个测试表test，数据显示如下：
select a.* from test a;
题主，如此，可否？
Ⅳ 如何在sql 中将表中一整列的汉字全部变成拼音首字母
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 到底应该怎么念
sql读作/ˈes kjuː ˈel/ "S-Q-L").
SQL(Structured Query Language)简称SQL(结构化查询语言，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
1986年10月，美国国家标准协会对SQL进行规范后，以此作为关系式数据库管理系统的标准语言（ANSI X3. 135-1986），1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以，实际上不同数据库系统之间的SQL不能完全相互通用。
Ⅵ SQL 获取名字拼音
做两个函数。一个取汉字拼音首字母，一个取完整拼音
下面代码是晚上搜到的：
--取汉字首字母函数
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
select '吖','A' union all select '八','B' union all
select '嚓','C' union all select '咑','D' union all
select '妸','E' union all select '发','F' union all
select '旮','G' union all select '铪','H' union all
select '丌','J' union all select '咔','K' union all
select '垃','L' union all select '呒','M' union all
select '拏','N' union all select '噢','O' union all
select '妑','P' union all select '七','Q' union all
select '呥','R' union all select '仨','S' union all
select '他','T' union all select '屲','W' union all
select '夕','X' union all select '丫','Y' union all
select '帀','Z'
select @strlen=len(@str),@re=''
while @strlen>0
begin
select top 1 @re=letter+@re,@strlen=@strlen-1
from @t a where chr<=substring(@str,@strlen,1)
order by chr desc
if @@rowcount=0
select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
go
--使用
select id,name,dbo.f_GetPy(name) from mytable
取完整拼音的跟上述f_GetPy函数类似，只是临时表要多写很多行，拼音有多少种组合就写多少行，大概六七百行吧。你自己找找相关的其他语言转拼音全码的代码，实现方式是相通的。
Ⅶ SQL语句如何实现将数据库表中某个字段的汉字批量转为拼音
sql可以实现 但是你要准备 建立一个庞大的字典库所有汉字拼音全存入 然后写函数一个个比对返回
我都是用程序写的 sql实现比较蛋痛
Ⅷ sql中怎么根据汉字的拼音首字母查询
--SQLServer:
---测试数据---
ifobject_id('[pactinfo]')isnotnulldroptable[pactinfo]
go
createtable[pactinfo]([ID]int,[pactname]varchar(4))
insert[pactinfo]
select1,'正常'unionall
select2,'中国'unionall
select3,'做饭'unionall
select4,'加发'
---引用前辈们的一个函数---
createfunctionf_GetPy(@strnvarchar(4000))
returnsnvarchar(4000)
as
begin
declare@strlenint,@renvarchar(4000)
declare@ttable(chrnchar(1)collateChinese_PRC_CI_AS,letternchar(1))
insertinto@t(chr,letter)
select'吖','A'unionallselect'八','B'unionall
select'嚓','C'unionallselect'咑','D'unionall
select'妸','E'unionallselect'发','F'unionall
select'旮','G'unionallselect'铪','H'unionall
select'丌','J'unionallselect'咔','K'unionall
select'垃','L'unionallselect'呒','M'unionall
select'拏','N'unionallselect'噢','O'unionall
select'妑','P'unionallselect'七','Q'unionall
select'呥','R'unionallselect'仨','S'unionall
select'他','T'unionallselect'屲','W'unionall
select'夕','X'unionallselect'丫','Y'unionall
select'帀','Z'
select@strlen=len(@str),@re=''
while@strlen>0
begin
selecttop1@re=letter+@re,@strlen=@strlen-1
from@tawherechr<=substring(@str,@strlen,1)
orderbychrdesc
if@@rowcount=0
select@re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
end
return(@re)
end
---查询---
select
*
from
[pactinfo]
where
left(dbo.f_GetPy(pactname),1)='Z'
---结果---
IDpactname
-------------------
1正常
2中国
3做饭
（所影响的行数为3行）