sql函数:成生文字的第一个英文字母

Posted on 2005-05-10 09:09 TomSun 阅读(429) 评论(0) 收藏举报

CREATE function udf_GetFirst_Py
2

(@Str varchar(500)='')
3

returns varchar(500)
4

as
5

begin
6

Declare @strlen int,
7

@return varchar(500),
8

@ii int
9

Declare @n int,
10

@c char(1),
11

@chn nchar(1)
12

--//初始化变量
13

Select @strlen=len(@str),@return='',@ii=0
14

--//确认输入不为空
15

if @strlen=0
16

begin
17

select @return='Please input chatacter!'
18

return(@return)
19

end
20

--//循环整个字符串,用拼音的首字母替换汉字
21

while @ii<@strlen
22

begin
23

select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
24

if @chn>'z' --//检索输入的字符串中有中文字符
25

select @n=@n+1 ,@c = case chn when @chn then
26

char(@n) else @c end
27

from( select top 27 * from (
28

select chn = '
29

吖'
30

union all
31

select '八'
32

union all
33

select '嚓'
34

union all
35

select '咑'
36

union all
37

select '妸'
38

union all
39

select '发'
40

union all
41

select '旮'
42

union all
43

select '铪'
44

union all
45

select '丌'
46

union all
47

select '丌'
48

union all
49

select '咔'
50

union all
51

select '垃'
52

union all
53

select '嘸'
54

union all
55

select '拏'
56

union all
57

select '噢'
58

union all
59

select '妑'
60

union all
61

select '七'
62

union all
63

select '呥'
64

union all
65

select '仨'
66

union all
67

select '他'
68

union all
69

select '屲'
70

union all
71

select '屲'
72

union all
73

select '屲'
74

union all
75

select '夕'
76

union all
77

select '丫'
78

union all
79

select '帀'
80

union all
81

select @chn) as a
82

order by chn COLLATE
83

Chinese_PRC_CI_AS
84

) as b
85

else set @c=@chn
86

set @return=@return+@c
87

end
88

return(@return)
89

end

刷新页面返回顶部

TomSun

导航

公告

sql函数:成生文字的第一个英文字母