❶ 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 update 更新语句
什么数据库，如果是oracle的提供给你,不用客气,就是分少了些。
CREATE OR REPLACE FUNCTION PUB_GET_PY(p_汉字 CHAR DEFAULT '%')
RETURN CHAR
IS
/********************************************************************
过程名称：PUB_GET_PY
功能：生成汉字拼音的首字符
********************************************************************/
v_汉字内码 NUMBER(8);
v_拼音 CHAR(1);
BEGIN
v_汉字内码 := ASCII(p_汉字);
IF v_汉字内码 BETWEEN 45217 AND 45252 THEN
v_拼音 := 'A';
ELSIF v_汉字内码 BETWEEN 45253 AND 45760 THEN
v_拼音 := 'B';
ELSIF v_汉字内码 BETWEEN 45761 AND 46317 THEN
v_拼音 := 'C';
ELSIF v_汉字内码 BETWEEN 46318 AND 46825 THEN
v_拼音 := 'D';
ELSIF v_汉字内码 BETWEEN 46826 AND 47009 THEN
v_拼音 := 'E';
ELSIF v_汉字内码 BETWEEN 47010 AND 47296 THEN
v_拼音 := 'F';
ELSIF v_汉字内码 BETWEEN 47297 AND 47613 THEN
v_拼音 := 'G';
ELSIF v_汉字内码 BETWEEN 47614 AND 48116 THEN
v_拼音 := 'H';
ELSIF v_汉字内码 BETWEEN 48117 AND 49061 THEN
v_拼音 := 'J';
ELSIF v_汉字内码 BETWEEN 49062 AND 49323 THEN
v_拼音 := 'K';
ELSIF v_汉字内码 BETWEEN 49324 AND 49895 THEN
v_拼音 := 'L';
ELSIF v_汉字内码 BETWEEN 49896 AND 50370 THEN
v_拼音 := 'M';
ELSIF v_汉字内码 BETWEEN 50371 AND 50613 THEN
v_拼音 := 'N';
ELSIF v_汉字内码 BETWEEN 50614 AND 50621 THEN
v_拼音 := 'O';
ELSIF v_汉字内码 BETWEEN 50622 AND 50925 THEN
v_拼音 := 'P';
ELSIF v_汉字内码 BETWEEN 50926 AND 51386 THEN
v_拼音 := 'Q';
ELSIF v_汉字内码 BETWEEN 51387 AND 51445 THEN
v_拼音 := 'R';
ELSIF v_汉字内码 BETWEEN 51446 AND 52217 THEN
v_拼音 := 'S';
ELSIF v_汉字内码 BETWEEN 52218 AND 52697 THEN
v_拼音 := 'T';
ELSIF v_汉字内码 BETWEEN 52698 AND 52979 THEN
v_拼音 := 'W';
ELSIF v_汉字内码 BETWEEN 52980 AND 53640 THEN
v_拼音 := 'X';
ELSIF v_汉字内码 BETWEEN 53641 AND 54480 THEN
v_拼音 := 'Y';
ELSIF v_汉字内码 BETWEEN 54481 AND 55289 THEN
v_拼音 := 'Z';
ELSE
v_拼音 := NULL;
END IF;
RETURN v_拼音;
END PUB_GET_PY;
CREATE OR REPLACE FUNCTION PUB_GET_STRING_PY(strs varchar2)
return varchar2
is
/***********************************
过程名称：PUB_GET_STRING_PY
功能：生成汉字字符串的拼音首字符
**********************************/
v_length number;
v_char varchar2(2);
v_charlength number;
v_charPY varchar2(2);
i int:=1;
myresult varchar2(500):='';
begin
--获取字符串长度
v_length:=length(strs);
while i<=v_length
loop
--按顺序取得字符
v_char:=substr(strs,i,1);
v_charlength:=lengthb(rtrim(v_char));
v_charPY:=rtrim(v_char);
--判断是否是汉字
if v_charlength=2 then
v_charPY:=pub_get_py(rtrim(v_char));
end if;
--将拼音进行连接
myresult:=myresult || v_charPY;
i:=i+1;
end loop;
return myresult;
end PUB_GET_STRING_PY;
/
❸ sql 取中文的拼音首字母
Create Function f_GetallPy(@chn nvarchar(100))
returns varchar(30)
as
begin
declare @i int,@j int,@result varchar(100)
set @result=''
set @i=len(@chn)
set @j=1
while @j<=@i
begin
set @result = @result + dbo.f_GetPy(substring(@chn,@j,1))
set @j=@j+1
end
return @result
end
这样你就明白了吧
❹ 怎么在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 获取名字拼音
做两个函数。一个取汉字拼音首字母，一个取完整拼音
下面代码是晚上搜到的：
--取汉字首字母函数
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函数类似，只是临时表要多写很多行，拼音有多少种组合就写多少行，大概六七百行吧。你自己找找相关的其他语言转拼音全码的代码，实现方式是相通的。
❻ 如何用db2的sql写一个获取汉字拼音首字母的
DECLARE @str VARCHAR(100)
SET @str = '汉字的首字母'
SELECT @str AS A, dbo.fun_getPY(@str) AS B
先执行上面的那个函数，然后在执行下面的那个语句，就可以得到你要的结果了。
❼ 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 Server 如何提取汉字首字母
代码如下：
USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[getPinYin] (@str varchar(500) = '')
RETURNS varchar(500) AS
BEGIN
Declare @strlen int,
@return varchar(500),
@ii int,
@c char(1),
@chn nchar(1)
--//初始化变量
Declare @pytable table(
chn char(2) COLLATE Chinese_PRC_CS_AS NOT NULL,
py char(1) COLLATE Chinese_PRC_CS_AS NULL,
PRIMARY KEY (chn)
)
insert into @pytable values('吖', 'A')
insert into @pytable values('八', 'B')
insert into @pytable values('嚓', 'C')
insert into @pytable values('咑', 'D')
insert into @pytable values('妸', 'E')
insert into @pytable values('发', 'F')
insert into @pytable values('旮', 'G')
insert into @pytable values('铪', 'H')
insert into @pytable values('丌', 'I')
insert into @pytable values('丌', 'J')
insert into @pytable values('咔', 'K')
insert into @pytable values('垃', 'L')
insert into @pytable values('呒', 'M')
insert into @pytable values('拏', 'N')
insert into @pytable values('噢', 'O')
insert into @pytable values('妑', 'P')
insert into @pytable values('七', 'Q')
insert into @pytable values('呥', 'R')
insert into @pytable values('仨', 'S')
insert into @pytable values('他', 'T')
--insert into @pytable values('屲', 'U')
--insert into @pytable values('屲', 'V')
insert into @pytable values('屲', 'W')
insert into @pytable values('夕', 'X')
insert into @pytable values('丫', 'Y')
insert into @pytable values('帀', 'Z')
select @strlen = len(@str), @return = '', @ii = 0
//循环整个字符串,用拼音的首字母替换汉字
while @ii < @strlen
begin
select @ii = @ii + 1, @chn = substring(@str , @ii, 1)
if @chn > 'z' --//检索输入的字符串中有中文字符
SELECT @c = max(py)
FROM @pytable
where chn <= @chn
else
set @c=@chn
set @return=@return+@c
end
return @return
END
❾ SQL SERVER 获取字符串拼音函数，求优化
按你这种写法看起来优化程度有限。可以考虑吧substring改成left。每次循环完把第一个字符去掉。可能比substring 快点。
另外有个思路。就是需要替换成字母的字已经是确认的了。我数了一下是23个字
那你循环23遍 吧对应的字替换成对应的字母。 这样循环次数就固定下来，应该比传进来的字符串一个个循环次数少 。效率肯定会高！
❿ sql存储过程获取汉字拼音头字母函数
复制代码
代码如下:
--函数
CREATE
function
fn_GetPy(@str
nvarchar(4000))
returns
nvarchar(4000)
--WITH
ENCRYPTION
as
begin
declare
@intLenint
declare
@strRetnvarchar(4000)
declare
@temp
nvarchar(100)
set
@intLen
=
len(@str)
set
@strRet
=
''
while
@intLen
>
0
begin
set
@temp
=
''
select
@temp
=
case
when
substring(@str,@intLen,1)
>=
'帀'
then
'Z'
when
substring(@str,@intLen,1)
>=
'丫'
then
'Y'
when
substring(@str,@intLen,1)
>=
'夕'
then
'X'
when
substring(@str,@intLen,1)
>=
'屲'
then
'W'
when
substring(@str,@intLen,1)
>=
'他'
then
'T'
when
substring(@str,@intLen,1)
>=
'仨'
then
'S'
when
substring(@str,@intLen,1)
>=
'呥'
then
'R'
when
substring(@str,@intLen,1)
>=
'七'
then
'Q'
when
substring(@str,@intLen,1)
>=
'妑'
then
'P'
when
substring(@str,@intLen,1)
>=
'噢'
then
'O'
when
substring(@str,@intLen,1)
>=
'拏'
then
'N'
when
substring(@str,@intLen,1)
>=
'呒'
then
'M'
when
substring(@str,@intLen,1)
>=
'垃'
then
'L'
when
substring(@str,@intLen,1)
>=
'咔'
then
'K'
when
substring(@str,@intLen,1)
>=
'丌'
then
'J'
when
substring(@str,@intLen,1)
>=
'铪'
then
'H'
when
substring(@str,@intLen,1)
>=
'旮'
then
'G'
when
substring(@str,@intLen,1)
>=
'发'
then
'F'
when
substring(@str,@intLen,1)
>=
'妸'
then
'E'
when
substring(@str,@intLen,1)
>=
'咑'
then
'D'
when
substring(@str,@intLen,1)
>=
'嚓'
then
'C'
when
substring(@str,@intLen,1)
>=
'八'
then
'B'
when
substring(@str,@intLen,1)
>=
'吖'
then
'A'
else
rtrim(ltrim(substring(@str,@intLen,1)))
end
--对于汉字特殊字符，不生成拼音码
if
(ascii(@temp)>127)
set
@temp
=
''
--对于英文中小括号，不生成拼音码
if
@temp
=
'('
or
@temp
=
')'
set
@temp
=
''
select
@strRet
=
@temp
+
@strRet
set
@intLen
=
@intLen
-
1
end
return
lower(@strRet)
end
go
--调用
select
dbo.fn_getpy('张三')
--返回：zs
答！：
2:
取汉字拼音首字母的存储过程
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