create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int declare @c char(1) set @n = 63select @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 '丌' 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 @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b return(@c) endgoselect dbo.f_ch2py('中') --Z select dbo.f_ch2py('国') --G select dbo.f_ch2py('人') --R select dbo.f_ch2py('镆') --M
有谁能告诉我这几句是什么意思: 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 '丌' 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 @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b
---在你的查询分析器里执行: --建立函数: create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int declare @c char(1) set @n = 63select @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 '丌' 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 @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b return(@c) end go---再建立函数: CREATE FUNCTION F_GetHelpCode ( @cName VARCHAR(20) ) RETURNS VARCHAR(12) AS BEGIN DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT SELECT @i=1, @L=0 , @cHelpCode='' while @L<=12 AND @i<=LEN(@cName) BEGIN SELECT @e=LOWER(SUBSTRING(@cname,@i,1)) SELECT @iAscii=ASCII(@e) IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95 SELECT @cHelpCode=@cHelpCode +@e ELSE IF @iAscii>=176 AND @iAscii <=247 SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e) ELSE SELECT @L=@L-1 SELECT @i=@i+1, @L=@L+1 END RETURN @cHelpCode END GO --完成了,现在你可以用下面的调用: select dbo.F_GetHelpCode('大力') --或 select dbo.F_GetHelpCode(你的列名) from 表
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @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
'丌' 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 @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
endgoselect dbo.f_ch2py('中') --Z
select dbo.f_ch2py('国') --G
select dbo.f_ch2py('人') --R
select dbo.f_ch2py('镆') --M
http://expert.csdn.net/Expert/topic/1266/1266960.xml
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
'丌' 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 @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
--建立函数:
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @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
'丌' 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 @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end
go---再建立函数:
CREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(20) )
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=12 AND @i<=LEN(@cName) BEGIN
SELECT @e=LOWER(SUBSTRING(@cname,@i,1))
SELECT @iAscii=ASCII(@e)
IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95
SELECT @cHelpCode=@cHelpCode +@e
ELSE
IF @iAscii>=176 AND @iAscii <=247
SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)
ELSE SELECT @L=@L-1
SELECT @i=@i+1, @L=@L+1 END
RETURN @cHelpCode
END
GO
--完成了,现在你可以用下面的调用:
select dbo.F_GetHelpCode('大力')
--或
select dbo.F_GetHelpCode(你的列名) from 表