/* 功能:根据返回所有汉字的首字母 调用:select [dbo].[fun_getPY]('真的好想你') */ Create FUNCTION [dbo].[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 UPPER(@PY) END GO
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
--获取拼音首字母函数 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 --调用 create table tb(col varchar(10)) insert into tb values('潇洒老乌龟') insert into tb values('孤星')go select 按拼音首字母 = dbo.f_GetPy(col) from tbdrop table tb drop function f_GetPy/* 按拼音首字母 ------------ XSLWG GX(2 行受影响) */你那个应该是设置个关系表,对应去找.
--得到汉字助记码 ----a.生成汉字列表(GB2312) select top 71 ID=IDENTITY(int,176,1) into #a from dbo.syscolumns a,dbo.syscolumns
select top 94 id=identity(int,161,1) into #b from dbo.syscolumns a,dbo.syscolumns select a.id,b.id,cast(a.id as binary(1)),cast(b.id as binary(1)), ch=cast( cast(a.id as BINARY(1))+cast(b.id as BINARY(1)) as CHAR(2) ) collate chinese_prc_cs_as_ks_ws from #a a,#b b where not(a.id=215 and b.id between 250 and 254) order by chcreate table py_zjm(zjm nchar(1) primary key,zjm1 nchar(1), zjm2 nchar(1)) --DROP TABLE PY_ZJM insert py_zjm SELECT 'A',N'吖',N'鏊' UNION ALL SELECT 'B',N'八',N'簿' UNION ALL SELECT 'C',N'嚓',N'错' UNION ALL SELECT 'D',N'哒',N'跺' UNION ALL SELECT 'E',N'屙',N'贰' UNION ALL SELECT 'F',N'发',N'馥' UNION ALL SELECT 'G',N'旮',N'过' UNION ALL SELECT 'H',N'铪',N'蠖' UNION ALL SELECT 'J',N'丌',N'竣' UNION ALL SELECT 'K',N'咔',N'廓' UNION ALL SELECT 'L',N'垃',N'雒' UNION ALL SELECT 'M',N'妈',N'穆' UNION ALL SELECT 'N',N'拿',N'糯' UNION ALL SELECT 'O',N'噢',N'沤' UNION ALL SELECT 'P',N'趴',N'曝' UNION ALL SELECT 'Q',N'七',N'群' UNION ALL SELECT 'R',N'蚺',N'箬' UNION ALL SELECT 'S',N'仨',N'锁' UNION ALL SELECT 'T',N'他',N'箨' UNION ALL SELECT 'W',N'哇',N'鋈' UNION ALL SELECT 'X',N'夕',N'蕈' UNION ALL SELECT 'Y',N'丫',N'蕴' UNION ALL SELECT 'Z',N'匝',N'做' go -- SELECT * FROM PY_ZJM --得到助记码的函数 create function dbo.f_dbo_zjm (@str nvarchar(4000)) returns nvarchar(4000) as begin declare @i int --取要查询的字中的第一个汉字 set @i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str) while @i>0 select --将找到的第一个汉字用对应的拼音首字母替换 @str=replace(@str,substring(@str,@i,1),zjm), --取下个要处理的汉字位置 @i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str) from dbo.py_zjm where substring(@str,@i,1) between zjm1 and zjm2 return(@str) end goif OBJECT_ID('tb') is not null drop table tb go create table tb(name nvarchar(1000)) insert tb select '北京,成都,上海,成都,重庆' declare @max int select @max=len(name) from tb order by 1 set ROWCOUNT @max select IDENTITY(int,1,1) as id,COL=1 into #a from dbo.syscolumns a,dbo.syscolumns b --drop table #a set ROWCOUNT 0 select py,name from ( select --drop table #aa SUBSTRING(a.py,b.id,CHARINDEX(',',a.py+',',b.id)-b.id) as py, SUBSTRING(a.name,b.id,CHARINDEX(',',a.name+',',b.id)-b.id) as name from (select dbo.f_dbo_zjm(name collate chinese_prc_cs_as_ks_ws) as py,name from tb)a,#a b where LEN(a.name)>=b.id and SUBSTRING(','+a.name,b.id,1)=',')a where a.py like 'C%'
功能:根据返回所有汉字的首字母
调用:select [dbo].[fun_getPY]('真的好想你')
*/
Create FUNCTION [dbo].[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 UPPER(@PY)
END
GO
(
@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
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
--调用
create table tb(col varchar(10))
insert into tb values('潇洒老乌龟')
insert into tb values('孤星')go
select 按拼音首字母 = dbo.f_GetPy(col) from tbdrop table tb
drop function f_GetPy/*
按拼音首字母
------------
XSLWG
GX(2 行受影响)
*/你那个应该是设置个关系表,对应去找.
--得到汉字助记码
----a.生成汉字列表(GB2312)
select top 71
ID=IDENTITY(int,176,1) into #a
from dbo.syscolumns a,dbo.syscolumns
select top 94
id=identity(int,161,1) into #b
from dbo.syscolumns a,dbo.syscolumns
select a.id,b.id,cast(a.id as binary(1)),cast(b.id as binary(1)),
ch=cast(
cast(a.id as BINARY(1))+cast(b.id as BINARY(1)) as CHAR(2)
) collate chinese_prc_cs_as_ks_ws
from #a a,#b b
where not(a.id=215 and b.id between 250 and 254)
order by chcreate table py_zjm(zjm nchar(1) primary key,zjm1 nchar(1),
zjm2 nchar(1)) --DROP TABLE PY_ZJM
insert py_zjm
SELECT 'A',N'吖',N'鏊'
UNION ALL SELECT 'B',N'八',N'簿'
UNION ALL SELECT 'C',N'嚓',N'错'
UNION ALL SELECT 'D',N'哒',N'跺'
UNION ALL SELECT 'E',N'屙',N'贰'
UNION ALL SELECT 'F',N'发',N'馥'
UNION ALL SELECT 'G',N'旮',N'过'
UNION ALL SELECT 'H',N'铪',N'蠖'
UNION ALL SELECT 'J',N'丌',N'竣'
UNION ALL SELECT 'K',N'咔',N'廓'
UNION ALL SELECT 'L',N'垃',N'雒'
UNION ALL SELECT 'M',N'妈',N'穆'
UNION ALL SELECT 'N',N'拿',N'糯'
UNION ALL SELECT 'O',N'噢',N'沤'
UNION ALL SELECT 'P',N'趴',N'曝'
UNION ALL SELECT 'Q',N'七',N'群'
UNION ALL SELECT 'R',N'蚺',N'箬'
UNION ALL SELECT 'S',N'仨',N'锁'
UNION ALL SELECT 'T',N'他',N'箨'
UNION ALL SELECT 'W',N'哇',N'鋈'
UNION ALL SELECT 'X',N'夕',N'蕈'
UNION ALL SELECT 'Y',N'丫',N'蕴'
UNION ALL SELECT 'Z',N'匝',N'做'
go
-- SELECT * FROM PY_ZJM
--得到助记码的函数
create function dbo.f_dbo_zjm
(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @i int
--取要查询的字中的第一个汉字
set @i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str)
while @i>0
select
--将找到的第一个汉字用对应的拼音首字母替换
@str=replace(@str,substring(@str,@i,1),zjm),
--取下个要处理的汉字位置
@i=patindex('%[吖-做]%' collate chinese_prc_cs_as_ks_ws,@str)
from dbo.py_zjm
where substring(@str,@i,1) between zjm1 and zjm2
return(@str)
end
goif OBJECT_ID('tb') is not null drop table tb
go
create table tb(name nvarchar(1000))
insert tb
select
'北京,成都,上海,成都,重庆'
declare @max int
select @max=len(name) from tb order by 1
set ROWCOUNT @max
select IDENTITY(int,1,1) as id,COL=1 into #a from dbo.syscolumns a,dbo.syscolumns b --drop table #a
set ROWCOUNT 0
select py,name
from
(
select --drop table #aa
SUBSTRING(a.py,b.id,CHARINDEX(',',a.py+',',b.id)-b.id) as py,
SUBSTRING(a.name,b.id,CHARINDEX(',',a.name+',',b.id)-b.id) as name
from (select dbo.f_dbo_zjm(name collate chinese_prc_cs_as_ks_ws) as py,name from tb)a,#a b
where LEN(a.name)>=b.id
and SUBSTRING(','+a.name,b.id,1)=',')a where a.py like 'C%'