Create
table
NumberToChar
--
--------用来保存数字和字母之间映射的表
(
number
int
, GetText
varchar
(
10
) )
--
字典表
select
*
from
Dictionary
--
联系人表
select
*
from
Contact
--
创建键盘表---------------------------------------------------------------------------------
create
table
Keys ( alpha
char
(
1
)
primary
key
, digit
char
(
1
) , )
insert
into
Keys(alpha, digit)
values
(
'
a
'
,
'
2
'
);
insert
into
Keys(alpha, digit)
values
(
'
b
'
,
'
2
'
);
insert
into
Keys(alpha, digit)
values
(
'
c
'
,
'
2
'
);
insert
into
Keys(alpha, digit)
values
(
'
d
'
,
'
3
'
);
insert
into
Keys(alpha, digit)
values
(
'
e
'
,
'
3
'
);
insert
into
Keys(alpha, digit)
values
(
'
f
'
,
'
3
'
);
insert
into
Keys(alpha, digit)
values
(
'
g
'
,
'
4
'
);
insert
into
Keys(alpha, digit)
values
(
'
h
'
,
'
4
'
);
insert
into
Keys(alpha, digit)
values
(
'
i
'
,
'
4
'
);
insert
into
Keys(alpha, digit)
values
(
'
j
'
,
'
5
'
);
insert
into
Keys(alpha, digit)
values
(
'
k
'
,
'
5
'
);
insert
into
Keys(alpha, digit)
values
(
'
l
'
,
'
5
'
);
insert
into
Keys(alpha, digit)
values
(
'
m
'
,
'
6
'
);
insert
into
Keys(alpha, digit)
values
(
'
n
'
,
'
6
'
);
insert
into
Keys(alpha, digit)
values
(
'
o
'
,
'
6
'
);
insert
into
Keys(alpha, digit)
values
(
'
p
'
,
'
7
'
);
insert
into
Keys(alpha, digit)
values
(
'
q
'
,
'
7
'
);
insert
into
Keys(alpha, digit)
values
(
'
r
'
,
'
7
'
);
insert
into
Keys(alpha, digit)
values
(
'
s
'
,
'
7
'
);
insert
into
Keys(alpha, digit)
values
(
'
t
'
,
'
8
'
);
insert
into
Keys(alpha, digit)
values
(
'
u
'
,
'
8
'
);
insert
into
Keys(alpha, digit)
values
(
'
v
'
,
'
8
'
);
insert
into
Keys(alpha, digit)
values
(
'
w
'
,
'
9
'
);
insert
into
Keys(alpha, digit)
values
(
'
x
'
,
'
9
'
);
insert
into
Keys(alpha, digit)
values
(
'
y
'
,
'
9
'
);
insert
into
Keys(alpha, digit)
values
(
'
z
'
,
'
9
'
);
select
*
from
Keys
--
创建联系人T9码速查表------------------------------------------------------------------
create
table
ContactT9Info ( id
int
not
null
REFERENCES
Contact(id), T9Code
varchar
(
100
)
not
null
,
PRIMARY
KEY
CLUSTERED
(id, T9Code) )
select
*
from
ContactT9Info
--
函数:取得汉字的拼音---------------------------------------------------------------------
select
dbo.getPY(
'
黄
'
)
create
function
getPY(
@word
varchar
(
5
))
returns
varchar
(
10
)
as
begin
declare
@py
varchar
(
10
)
set
@py
=
(
select
top
1
py
from
dictionary
where
words
like
'
%
'
+
@word
+
'
%
'
)
return
(
@py
)
end
go
--
函数:取得拼音的T9码---------------------------------------------------------------------
select
dbo.getT9CodeFromPY(
'
huang
'
)
create
function
getT9CodeFromPY(
@pinyin
varchar
(
10
))
returns
varchar
(
10
)
as
begin
declare
@i
int
,
@length
int
,
@alpha
char
(
1
),
@code
varchar
(
10
)
set
@i
=
1
set
@length
=
len
(
@pinyin
)
set
@code
=
''
while
(
@i
<=
@length
)
begin
--
对拼音的每个字母进行处理
set
@alpha
=
substring
(
@pinyin
,
@i
,
1
)
set
@code
=
@code
+
dbo.getKeyFromAlpha(
@alpha
)
set
@i
=
@i
+
1
end
return
@code
end
go
--
函数:取得字母的键码---------------------------------------------------------------------
select
dbo.getKeyFromAlpha(
'
h
'
)
create
function
getKeyFromAlpha(
@alpha
char
(
1
))
returns
char
(
1
)
as
begin
return
(
select
digit
from
Keys
where
alpha
=
@alpha
)
end
go
--
存储过程:执行此存储过程创建联系人T9码速查信息数据-------------------------------------------
create
procedure
sp_createContactT9Info
as
begin
declare
@i
int
,
@length
int
,
@word
varchar
(
5
),
@code
varchar
(
100
)
declare
@contactid
int
,
@name
varchar
(
20
)
--
先清空ContactT9Info表的数据
delete
from
ContactT9Info
--
声明一个游标取出contact表的数据,并打开此游标
declare
contact_cursor
cursor
for
select
id, name
from
contact
open
contact_cursor
fetch
next
from
contact_cursor
into
@contactid
,
@name
--
检查@@fetch_status的值判断是否还能取得记录
while
@@fetch_status
=
0
begin
--
对contact_cursor中取得的每条记录进行操作
set
@length
=
len
(
@name
)
set
@i
=
@length
set
@code
=
''
while
(
@i
>=
1
)
begin
--
从姓名的最后一个字开始,分别对每个字进行处理
set
@word
=
substring
(
@name
,
@i
,
1
)
set
@code
=
dbo.getT9CodeFromPY(dbo.getPY(
@word
))
+
@code
insert
into
ContactT9Info(id, T9Code)
values
(
@contactid
,
@code
)
set
@i
=
@i
-
1
end
fetch
next
from
contact_cursor
into
@contactid
,
@name
end
close
contact_cursor
deallocate
contact_cursor
print
'
success!
'
end
go
exec
sp_CreateContactT9Info
--
存储过程:通过输入T9码速查联系人----------------------------------------------------------
create
procedure
sp_getContactByT9
@T9Code
varchar
(
100
)
as
select
c.
*
from
Contact c
where
exists
(
select
*
from
ContactT9Info i
where
i.id
=
c.id
and
i.T9Code
like
@T9Code
+
'
%
'
)
go
exec
sp_getContactByT9
'
264
'
--
存储过程:显示所有联系人的信息
create
procedure
sp_showContactInfo
as
select
c.
*
,i.T9Code
from
Contact c
left
join
ContactT9Info i
on
c.id
=
i.id
go
exec
sp_showContactInfo
附加一部分代码,我已经忘了是做什么用的了,看来注释确实是个好习惯
select
*
from
NumberToChar
drop
function
Get_StrArrayLength
CREATE
function
Get_StrArrayLength (
@str
varchar
(
1024
),
--
要分割的字符串
@split
varchar
(
10
)
--
分隔符号
)
returns
int
as
begin
declare
@location
int
declare
@start
int
declare
@length
int
set
@str
=
ltrim
(
rtrim
(
@str
))
set
@location
=
charindex
(
@split
,
@str
)
set
@length
=
1
while
@location
<>
0
begin
set
@start
=
@location
+
1
set
@location
=
charindex
(
@split
,
@str
,
@start
)
set
@length
=
@length
+
1
end
return
@length
end