Question

SQL Server 如何提取汉字首字母

SQL Server数据库内存放如下数据

河南-轴承-22
河北-螺丝-23
江苏-钢板-23

我想通过查询得到这样的数据
hn-zc-22
hb-ls-23
js-gb-23

请问SQL语句该如何写啊！！

Answer 1

代码如下：
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

Answer 2

这个我以前写过， 要用 C# 来写， 然后 发布到 SQL Server 上面去。

你要是会 C# 的话， 倒可以尝试尝试， 否则就不必下载附件了。

那个项目还引用了  Microsoft.International.Converters.PinYinConverter 这个类库。 

你可能还要去微软网站找来下载一下。

或者用其他的算法， 通过汉字, 返回拼音的。

Answer 3

思路比较明确，建立获取汉字首字母拼音的函数，然后调用自定义的函数，执行查询，就可以得到结果。

Answer 4

这样：
select dbo.getpyFirst(fieldname) from tablename
但需要先建立以下函数：
create function GetPyFirst
(@Str varchar(500)='')
returns varchar(500)
begin

declare @strlen int,@return varchar(500),@ii int
declare @n int,@c char(1),@chn nchar(1)

select @strlen=len(@str),@return='',@ii=0
set @ii=0
while @ii<@strlen
begin
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'
select @n = @n +1
,@c = case chn when @chn then char(@n) else @c end
from(
select top 27 * from (
select chn = '吖'
union all select '八'
union all select '嚓'
union all select '咑'
union all select '妸'
union all select '发'
union all select '旮'
union all select '铪'
union all select '丌' --because have no 'i'
union all select '丌'
union all select '咔'
union all select '垃'
union all select '呒'
union all select '拏'
union all select '噢'
union all select '妑'
union all select '七'
union all select '呥'
union all select '仨'
union all select '他'
union all select '屲' --no 'u'
union all select '屲' --no 'v'
union all select '屲'
union all select '夕'
union all select '丫'
union all select '帀'
union all select @chn) as a
order by chn --COLLATE Chinese_PRC_CI_AS
) as b
else set @c=@chn
set @return=@return
end
return @return
end

GO