sql取拼音首字母

③ 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语句提取出中文的拼音首字母

正好最近收藏了一个 你可以看下思路
--将中文字符串转化成文字首拼音的组合
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 取中文的拼音首字母

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

这样你就明白了吧

⑥ 如何用db2的sql写一个获取汉字拼音首字母的

DECLARE @str VARCHAR(100)
SET @str = '汉字的首字母'
SELECT @str AS A, dbo.fun_getPY(@str) AS B

先执行上面的那个函数，然后在执行下面的那个语句，就可以得到你要的结果了。

⑦ sql中怎么根据汉字的拼音首字母查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- SQLServer:
---测试数据---
if object_id('[pactinfo]') is not null drop table [pactinfo]
go
create table [pactinfo]([ID] int,[pactname] varchar(4))
insert [pactinfo]
select 1,'正常' union all
select 2,'中国' union all
select 3,'做饭' union all
select 4,'加发'

---引用前辈们的一个函数---
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

---查询---
select
*
from
[pactinfo]
where
left(dbo.f_GetPy(pactname),1)='Z'

---结果---
ID pactname
----------- --------
1 正常
2 中国
3 做饭

（所影响的行数为 3 行）