求sql取字符串的首字母的函数

yuji821 2008-06-23 11:57:20

如中国好大返回 'Z'
火炬返回 'H'
奥运会返回 'A'

yuji2008 返回 'Y'
U001 返回 'U'
假如首位是数字或特殊字符，就直接返回首位
如：
3535sfsf 返回'3'
#$%sfsdf43646 返回'#'

返回大写字母，要求要用sql server 2000函数写

...全文

1611 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

wzy_love_sly 2008-06-23

打赏
举报

If Exists(Select * From sysobjects T Where T.id = object_id(N'HZ2PY') AND xtype IN(N'FN', N'IF', N'TF'))

   drop Function HZ2PY

go

Create Function HZ2PY

(

    @cString nVarChar (200)

)

Returns nVarChar(100)

AS

Begin

  Declare @nFor    Int                    --字符串循环用

  Declare @nPYFor  Int                    --拼音串循环用

  Declare @cPYString  VarChar(100)        --拼音串

  Declare @cCurChar VarChar(2)            --当前字符

  Declare @vReturn VarChar (100)                   ----将返回的结果

  Set @cPYString = '吖八嚓咑妸发旮铪讥讥咔垃呣拿讴趴七呥仨他哇哇哇夕丫匝咗'

  Set @vReturn = ''

  Set @nFor = 0

  While @nFor < Len(@cString)

    Begin

    Set @nFor = @nFor + 1

    Set @cCurChar = Substring(@cString,@nFor,1)

    Set @nPYFor = 0

    While @nPYFor < len(@cPYString)

      Begin

      Set @nPYFor = @nPYFor + 1

      If @cCurChar < Substring(@cPYString,@nPYFor,1)

        Begin

          Set @vReturn = Rtrim(@vReturn) + (Case When @nPYFor <> 1 Then Char(63 + @nPYFor) Else @cCurChar End)

          Break

        End

      Else

        Begin

          Continue

        End

      End

    End

  Return @vReturn

End





declare @tb table (id int,name varchar(20))

insert into @tb select 1,'波'

insert into @tb select 2,'病'

insert into @tb select 3,'啊'

insert into @tb select 4,'吹'

insert into @tb select 5,'崔'



select *,dbo.hz2py(name) as '首字母' from @tb

中国风 2008-06-23

打赏
举报

http://topic.csdn.net/u/20071212/20/b660e358-052c-456a-9bdb-665476ed7db8.html

feng2112 2008-06-23

打赏
举报

mark...
学习and收藏了!

hanjs 2008-06-23

打赏
举报

这些可以处理所有ＧＢＫ的汉字么？？？？？？

hanjs 2008-06-23

打赏
举报

谁能说下实现的原理啊？？

学习了！

-狙击手- 2008-06-23

打赏
举报

--返回汉字首字母 



create  function [dbo].[chinese_firstletter] 

( 

  @str nvarchar(1) 

) 

returns nvarchar(1) 

as 

begin 

  declare @word nchar(1),@PY nvarchar(4000) 

  set @PY='' 



    set @word=left(@str,1) 

    --如果非汉字字符，返回原字符 

    set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 

                    then (  

                    select top 1 PY  

                    from  

                    (  

                    select 'A' as PY,N'驁' as word 

                    union all select 'B',N'簿' 

                    union all select 'C',N'錯' 

                    union all select 'D',N'鵽' 

                    union all select 'E',N'樲' 

                    union all select 'F',N'鰒' 

                    union all select 'G',N'腂' 

                    union all select 'H',N'夻' 

                    union all select 'J',N'攈' 

                    union all select 'K',N'穒' 

                    union all select 'L',N'鱳' 

                    union all select 'M',N'旀' 

                    union all select 'N',N'桛' 

                    union all select 'O',N'漚' 

                    union all select 'P',N'曝' 

                    union all select 'Q',N'囕' 

                    union all select 'R',N'鶸' 

                    union all select 'S',N'蜶' 

                    union all select 'T',N'籜' 

                    union all select 'W',N'鶩' 

                    union all select 'X',N'鑂' 

                    union all select 'Y',N'韻' 

                    union all select 'Z',N'咗' 

                    ) T  

                    where word>=@word collate Chinese_PRC_CS_AS_KS_WS  

                    order by PY ASC 

                          )  

                    else @word  

                end) 

    set @str=right(@str,len(@str)-1) 



  return @PY 

end 



go



Select dbo.chinese_firstletter('福田区第五医院')



drop function chinese_firstletter



/*



     

---- 

F



（所影响的行数为 1 行）

*/

yuji821 2008-06-23

打赏
举报

直接返回一个，照楼上那样做会影响效率和性能的

changweishao 2008-06-23

打赏
举报

--使用上面的拼音函数，

Select Left(dbo.fun_getPY('福田区第五医院'),1)

yuji821 2008-06-23

打赏
举报

楼上的不对，我只要返回第一个字的首字母，不要全部首字母
福田区第五医院你返回的是 'FTQDWYY' 我只要返回'F'

Novelty 2008-06-23

打赏
举报

--返回汉字首字母

create function [dbo].[chinese_firstletter]
(
@str nvarchar(1)
)
returns nvarchar(1)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符，返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else @word
end)
set @str=right(@str,len(@str)-1)
end
return @PY
end

liangCK 2008-06-23

打赏
举报

  Create  function fun_getPY 

 ( 

    @str nvarchar(4000) 

 ) 

returns nvarchar(4000) 

as 

begin 

  declare @word nchar(1),@PY nvarchar(4000) 



  set @PY='' 



  while len(@str)>0 

  begin 

    set @word=left(@str,1) 



    --假如非汉字字符，返回原字符 

    set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 

               then (  

                            select top 1 PY  

                            from  

                            (  

                             select 'A' as PY,N'驁' as word 

                             union all select 'B',N'簿' 

                             union all select 'C',N'錯' 

                     union all select 'D',N'鵽' 

                     union all select 'E',N'樲' 

                     union all select 'F',N'鰒' 

                     union all select 'G',N'腂' 

                     union all select 'H',N'夻' 

                     union all select 'J',N'攈' 

                     union all select 'K',N'穒' 

                     union all select 'L',N'鱳' 

                     union all select 'M',N'旀' 

                     union all select 'N',N'桛' 

                     union all select 'O',N'漚' 

                     union all select 'P',N'曝' 

                     union all select 'Q',N'囕' 

                     union all select 'R',N'鶸' 

                     union all select 'S',N'蜶' 

                     union all select 'T',N'籜' 

                     union all select 'W',N'鶩' 

                     union all select 'X',N'鑂' 

                     union all select 'Y',N'韻' 

                     union all select 'Z',N'咗' 

                      ) T  

                   where word>=@word collate Chinese_PRC_CS_AS_KS_WS  

                   order by PY ASC 

                          )  

                      else @word  

                 end) 

    set @str=right(@str,len(@str)-1) 

  end 



  return @PY 



end