34,590
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [c_py] (
[chn] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[py] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
PRIMARY KEY CLUSTERED
(
[chn]
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
INSERT c_py(chn,py) values("吖","A")
INSERT c_py(chn,py) values("八","B")
INSERT c_py(chn,py) values("嚓","C")
INSERT c_py(chn,py) values("咑","D")
INSERT c_py(chn,py) values("妸","E")
INSERT c_py(chn,py) values("发","F")
INSERT c_py(chn,py) values("旮","G")
INSERT c_py(chn,py) values("铪","H")
INSERT c_py(chn,py) values("丌","J")
INSERT c_py(chn,py) values("咔","K")
INSERT c_py(chn,py) values("垃","L")
INSERT c_py(chn,py) values("嘸","M")
INSERT c_py(chn,py) values("拏","N")
INSERT c_py(chn,py) values("噢","O")
INSERT c_py(chn,py) values("妑","P")
INSERT c_py(chn,py) values("七","Q")
INSERT c_py(chn,py) values("呥","R")
INSERT c_py(chn,py) values("仨","S")
INSERT c_py(chn,py) values("他","T")
INSERT c_py(chn,py) values("屲","W")
INSERT c_py(chn,py) values("夕","X")
INSERT c_py(chn,py) values("丫","Y")
INSERT c_py(chn,py) values("帀","Z")
go
--问题代码:(都是函数)
--函数一:
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @py char(1)
select top 1 @py = py
from c_py (nolock)
where chn <= @chn
order by chn COLLATE Chinese_PRC_CI_AS desc
return(isnull(@py,''))
end
go
--函数二:
create function f_st2zjm(@st nvarchar(4000))
returns varchar(100) --总笔数,字母和数字按一笔算。
as
begin
declare @bh varchar(100)
declare @n int
set @n = 1
set @bh = ''
while @n <= len(@st) and @n < 31
begin
if unicode(substring(@st,@n,1)) between 48 and 57 --数字
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 65 and 90 --大写字母
set @bh = @bh + substring(@st,@n,1)
else if unicode(substring(@st,@n,1)) between 97 and 122 --小写
set @bh = @bh + upper(substring(@st,@n,1))
else if substring(@st,@n,1) = '参' --多音字
set @bh = @bh + 'S'
else if unicode(substring(@st,@n,1)) between 19968 and 40869 --汉字
set @bh = @bh + dbo.f_ch2py(substring(@st,@n,1))
set @n = @n + 1
end
return(@bh)
end
go
select chn=dbo.f_st2zjm(chn),py=dbo.f_st2zjm(py) from c_py
/*
chn py
---------- ----------
A A
B B
C C
D D
E E
F F
G G
H H
J J
K K
L L
M M
N N
O O
P P
Q Q
R R
S S
T T
W W
X X
Y Y
Z Z
(23 row(s) affected)
*/
drop function dbo.f_ch2py
drop function dbo.f_st2zjm
drop table c_py