--@Char 输入的字符串 CREATE function dbo.GetPY_Pqs (@Char varchar(100)) returns varchar(100) as begin --字符串长度 返回数据 遍历字符串位置 遍历的字符 declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100) --初始化数据 --set @str='龙岂池中物' set @i_Len = len(@Char) set @c_Return = '' set @i_pos = 0 --遍历字符串 while (@i_pos < @i_Len ) begin set @i_pos = @i_pos + 1 set @c_CN = substring(@Char, @i_pos, 1) if (@c_CN > 'z' ) --中文处理,查询匹配 begin set @c = case when @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫' then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W' when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S' when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q' when @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M' when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and @c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H' when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D' when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' Else '' End -- select top 1 @c = Code from Code_PY where name <= @c_CN end else begin --过滤字符(除了字符、数字和'.'外的所有字符) if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.') set @c=@c_CN else set @c='' end
set @c_Return=@c_Return+isnull(@c ,'') end return upper(@c_Return)end create table table_pqs(id int identity(1,1),name varchar(100) not null, Pym varchar(100)) insert into table_pqs (name) select '都是' union all select '对方'select id,name,dbo.getPy (name) as Pym from table_pqsdrop table table_pqs结果 ----------------------------------------- id name Pym ----------- -------------- --------------------------------- 1 都是 DS 2 对方 DF (所影响的行数为 2 行)
如果是拼音字头的话,下面这个函数基本够用--获取字符串拼音字头--@Char 输入的字符串 CREATE function dbo.GetPY_Pqs (@Char varchar(100)) returns varchar(100) as begin--字符串长度 返回数据 遍历字符串位置 遍历的字符 declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100) --初始化数据 --set @str='龙岂池中物' set @i_Len = len(@Char) set @c_Return = '' set @i_pos = 0 --遍历字符串 while (@i_pos < @i_Len ) begin set @i_pos = @i_pos + 1 set @c_CN = substring(@Char, @i_pos, 1) if (@c_CN > 'z' ) --中文处理,查询匹配 begin set @c = case when @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫' then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W' when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S' when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q' when @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M' when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and @c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H' when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D' when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' Else '' End -- select top 1 @c = Code from Code_PY where name <= @c_CN end else begin --过滤字符(除了字符、数字和'.'外的所有字符) if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.') set @c=@c_CN else set @c='' endset @c_Return=@c_Return+isnull(@c ,'') end return upper(@c_Return)end create table table_pqs(id int identity(1,1),name varchar(100) not null, Pym varchar(100)) insert into table_pqs (name) select '都是' union all select '对方'select id,name,dbo.getPy_pqs (name) as Pym from table_pqsdrop table table_pqs结果 ----------------------------------------- id name Pym ----------- -------------- --------------------------------- 1 都是 DS 2 对方 DF (所影响的行数为 2 行)
http://sdlyi.bokee.com/2701149.html
http://www.blog.ztlz.net/user1/1/archives/2005/1409.shtml
--@Char 输入的字符串
CREATE function dbo.GetPY_Pqs (@Char varchar(100))
returns varchar(100) as
begin --字符串长度 返回数据 遍历字符串位置 遍历的字符
declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100)
--初始化数据
--set @str='龙岂池中物'
set @i_Len = len(@Char)
set @c_Return = ''
set @i_pos = 0
--遍历字符串
while (@i_pos < @i_Len )
begin
set @i_pos = @i_pos + 1
set @c_CN = substring(@Char, @i_pos, 1)
if (@c_CN > 'z' )
--中文处理,查询匹配
begin
set @c = case when @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫' then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W'
when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S' when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q'
when @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M'
when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and @c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H'
when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D'
when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' Else '' End
-- select top 1 @c = Code from Code_PY where name <= @c_CN
end
else
begin
--过滤字符(除了字符、数字和'.'外的所有字符)
if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.')
set @c=@c_CN
else
set @c=''
end
set @c_Return=@c_Return+isnull(@c ,'')
end
return upper(@c_Return)end
create table table_pqs(id int identity(1,1),name varchar(100) not null, Pym varchar(100))
insert into table_pqs (name)
select '都是' union all select '对方'select id,name,dbo.getPy (name) as Pym from table_pqsdrop table table_pqs结果
-----------------------------------------
id name Pym
----------- -------------- ---------------------------------
1 都是 DS
2 对方 DF
(所影响的行数为 2 行)
CREATE function dbo.GetPY_Pqs (@Char varchar(100))
returns varchar(100) as
begin--字符串长度 返回数据 遍历字符串位置 遍历的字符
declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100)
--初始化数据
--set @str='龙岂池中物'
set @i_Len = len(@Char)
set @c_Return = ''
set @i_pos = 0
--遍历字符串
while (@i_pos < @i_Len )
begin
set @i_pos = @i_pos + 1
set @c_CN = substring(@Char, @i_pos, 1)
if (@c_CN > 'z' )
--中文处理,查询匹配
begin
set @c = case when @c_CN>='帀' then 'Z' when @c_CN>='丫' and @c_CN<'帀' then 'Y' when @c_CN>='夕' and @c_CN<'丫' then 'X' when @c_CN>='屲' and @c_CN<'夕' then 'W'
when @c_CN>='他' and @c_CN<'屲' then 'T' when @c_CN>='仨' and @c_CN<'他' then 'S' when @c_CN>='呥' and @c_CN<'仨' then 'R' when @c_CN>='七' and @c_CN<'呥' then 'Q'
when @c_CN>='妑' and @c_CN<'七' then 'P' when @c_CN>='噢' and @c_CN<'妑' then 'O' when @c_CN>='拏' and @c_CN<'噢' then 'N' when @c_CN>='嘸' and @c_CN<'拏' then 'M'
when @c_CN>='垃' and @c_CN<'嘸' then 'L' when @c_CN>='咔' and @c_CN<'垃' then 'K' when @c_CN>='丌' and @c_CN<'咔' then 'J' when @c_CN>='铪' and @c_CN<'丌' then 'H'
when @c_CN>='旮' and @c_CN<'铪' then 'G' when @c_CN>='发' and @c_CN<'旮' then 'F' when @c_CN>='妸' and @c_CN<'发' then 'E' when @c_CN>='咑' and @c_CN<'妸' then 'D'
when @c_CN>='嚓' and @c_CN<'咑' then 'C' when @c_CN>='八' and @c_CN<'嚓' then 'B' when @c_CN>='吖' and @c_CN<'八' then 'A' Else '' End
-- select top 1 @c = Code from Code_PY where name <= @c_CN
end
else
begin
--过滤字符(除了字符、数字和'.'外的所有字符)
if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.')
set @c=@c_CN
else
set @c=''
endset @c_Return=@c_Return+isnull(@c ,'')
end
return upper(@c_Return)end
create table table_pqs(id int identity(1,1),name varchar(100) not null, Pym varchar(100))
insert into table_pqs (name)
select '都是' union all select '对方'select id,name,dbo.getPy_pqs (name) as Pym from table_pqsdrop table table_pqs结果
-----------------------------------------
id name Pym
----------- -------------- ---------------------------------
1 都是 DS
2 对方 DF
(所影响的行数为 2 行)