有如下表Table
数据如下
id name names
1 广州
2 北京
3 河南
4 世界大地
现在我想实现的name生成它拼音字母开头的那个字
id name names
1 广州 GZ
2 北京 BJ
3 河南 HN
4 世界大地 SJDD
这样的效果!
数据如下
id name names
1 广州
2 北京
3 河南
4 世界大地
现在我想实现的name生成它拼音字母开头的那个字
id name names
1 广州 GZ
2 北京 BJ
3 河南 HN
4 世界大地 SJDD
这样的效果!
--[收藏(sql server 2000)]在sql中创建用户自定义拼音函数: create function f_GetPy(@Str nvarchar(400))
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 @t 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)as names from [table]
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
endcreate table tb(name varchar(10))
insert into tb select '齐国'
insert into tb select '楚国'
insert into tb select '燕国'
insert into tb select '韩国'
insert into tb select '赵国'
insert into tb select '胃国'
insert into tb select '秦国'select name,left(dbo.fun_getPY(name),1) as china from tb
order by left(dbo.fun_getPY(name),1)
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
http://www.cnblogs.com/Ihaveadream/archive/2008/04/24/1169570.html
use [csdn]
go
/*--获得汉字字符串的首字母--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetPy]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fGetPy]
GO--创建取拼音函数
create function fGetPy(@Str varchar(500)='')
returns varchar(500)
as
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--while循环开始
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'--if开始
--此处只有一个 select 语句,原理以“字符串排序以及ASCII码表”:
select @n = @n +1,@c = --★★★select★★★
case chn --case开始
when @chn then char(@n) --case分支
else @c --case分支
end --case结束
from( --★★★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 --if对应的else
set @c=@chn
--if结束 set @return=@return+@c
end--while循环结束
return(@return)
end--函数实现结束go
--测试
select dbo.fgetpy('魏保光') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('刘子良') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('吴过') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('东北') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('王大海八丫') as 王大海八丫,dbo.fgetpy('服務地圖') as 服務地圖--删除拼音函数
--drop function fgetpy
但我只知道一次只能生成一个!
不知道怎么样可以把它N条记录同时生成出来了
select dbo.fGetPy('广州')
create table [Tb] (id int,name varchar(8),names sql_variant)
insert into [Tb]
select 1,'广州',null union all
select 2,'北京',null union all
select 3,'河南',null union all
select 4,'世界大地',null
select id, name, dbo.f_GetPy(name)as names from [tb]
/*
1 广州 GZ
2 北京 BJ
3 河南 HN
4 世界大地 SJDD
*/
select 列名,dbo.fGetPy(列名) from 表
http://topic.csdn.net/u/20070403/11/fe58d993-886c-4874-989e-f16007b3597f.html
-----
這貼。--最早見到j9988寫的