公司名称首字母做为它的英文名

biaozon 2011-03-12 10:41:10

我想取公司名称首字母做为它的英文名

如一张表a 中有公司名称,英文名

公司名称,英文名
创维科技 CWKJ

...全文

409 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

gogodiy 2011-03-14

打赏
举报

dawugui厉害～～

-晴天 2011-03-12

打赏
举报

create function f_GetPy(@str nvarchar(4000))

returns nvarchar(4000)

as

begin

declare @i int

declare @t table(id int,chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))

insert into @t

  select 1,'吖','A' union all select 2,'八','B' union all

  select 3,'嚓','C' union all select 4,'咑','D' union all

  select 5,'妸','E' union all select 6,'发','F' union all

  select 7,'旮','G' union all select 8,'铪','H' union all

  select 9,'丌','J' union all select 10,'咔','K' union all

  select 11,'垃','L' union all select 12,'嘸','M' union all

  select 13,'拏','N' union all select 14,'噢','O' union all

  select 15,'妑','P' union all select 16,'七','Q' union all

  select 17,'呥','R' union all select 18,'仨','S' union all

  select 19,'他','T' union all select 20,'屲','W' union all

  select 21,'夕','X' union all select 22,'丫','Y' union all

  select 23,'帀','Z' union all select 25,'',''

  set @i=1

  while @i<=len(@str)

	select @str=stuff(@str,@i,1,a.letter),@i=@i+1

		from @t a inner join @t b on a.id=b.id-1

		where substring(@str,@i,1)>=a.chr and substring(@str,@i,1)<b.chr

  return(@str)

end

go

create table tb(公司名称 nvarchar(10),英文名 varchar(10))

insert into tb(公司名称) select '创维科技'

go

update tb set [英文名]=dbo.f_GetPy([公司名称])

select * from tb

/*

公司名称       英文名

---------- ----------

创维科技       CWKJ



(1 行受影响)



*/

go

drop table tb

drop function dbo.f_GetPy

--小F-- 2011-03-12

打赏
举报

--返回汉字首字母 



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 行）

*/

AcHerat 2011-03-12

打赏
举报

收藏收藏！！！

快溜 2011-03-12

打赏
举报

[Quote=引用 3 楼 dawugui 的回复:]
这个也行.

SQL code
[code=SQL]--获取拼音首字母函数
create function f_GetPy(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) col……
[/Quote]
收藏，不解释

快溜 2011-03-12

打赏
举报

去逗号后面第一个？

dawugui 2011-03-12

打赏
举报

这个也行.

[code=SQL]--获取拼音首字母函数

create function f_GetPy(@str nvarchar(4000))

returns nvarchar(4000)

as

begin

declare @strlen int,@re nvarchar(4000)

declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))

insert into @t(chr,letter)

  select '吖','A' union all select '八','B' union all

  select '嚓','C' union all select '咑','D' union all

  select '妸','E' union all select '发','F' union all

  select '旮','G' union all select '铪','H' union all

  select '丌','J' union all select '咔','K' union all

  select '垃','L' union all select '嘸','M' union all

  select '拏','N' union all select '噢','O' union all

  select '妑','P' union all select '七','Q' union all

  select '呥','R' union all select '仨','S' union all

  select '他','T' union all select '屲','W' union all

  select '夕','X' union all select '丫','Y' union all

  select '帀','Z'

  select @strlen=len(@str),@re=''

  while @strlen>0

  begin

    select top 1 @re=letter+@re,@strlen=@strlen-1

      from @t a where chr<=substring(@str,@strlen,1)

      order by chr desc

    if @@rowcount=0

      select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1

  end

  return(@re)

end

go

--调用

create table tb(col varchar(10))

insert into tb values('潇洒老乌龟')

insert into tb values('孤星')



go

select 按拼音首字母 = dbo.f_GetPy(col) from tb



drop table tb

drop function f_GetPy



/*

按拼音首字母

------------

XSLWG

GX



(2 行受影响)

*/

[/code]

dawugui 2011-03-12

打赏
举报

参考如下:

--获取拼音首字母函数

create function f_GetPy(@str nvarchar(4000))

returns nvarchar(4000)

as

begin

declare @strlen int,@re nvarchar(4000)

declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))

insert into @t(chr,letter)

  select '吖','A' union all select '八','B' union all

  select '嚓','C' union all select '咑','D' union all

  select '妸','E' union all select '发','F' union all

  select '旮','G' union all select '铪','H' union all

  select '丌','J' union all select '咔','K' union all

  select '垃','L' union all select '嘸','M' union all

  select '拏','N' union all select '噢','O' union all

  select '妑','P' union all select '七','Q' union all

  select '呥','R' union all select '仨','S' union all

  select '他','T' union all select '屲','W' union all

  select '夕','X' union all select '丫','Y' union all

  select '帀','Z'

  select @strlen=len(@str),@re=''

  while @strlen>0

  begin

    select top 1 @re=letter+@re,@strlen=@strlen-1

      from @t a where chr<=substring(@str,@strlen,1)

      order by chr desc

    if @@rowcount=0

      select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1

  end

  return(@re)

end

go

--调用

select * from a order by case Name when '其他' then 1 else 0 end,dbo.f_GetPy(Name)











--获取字符串拼音字头

--Pqs 2006-11-20

--@Char 输入的字符串

CREATE function dbo.GetPY (@Char varchar(100))  

returns varchar(100) as 

begin

 --字符串长度		返回数据		遍历字符串位置		遍历的字符

 declare @i_Len int, @c_Return varchar(100), @i_pos int,@c varchar(2),@c_CN varchar(100)

 --初始化数据

 --set @str='龙岂池中物'

 set @i_Len = len(@Char)

 set @c_Return = ''

 set @i_pos = 0 

 --遍历字符串 

 while (@i_pos < @i_Len )

 begin

  set @i_pos = @i_pos + 1

  set @c_CN = substring(@Char, @i_pos, 1)

  if (@c_CN > 'z' )

  --中文处理，查询匹配

  begin

         set @c = case when @c_CN>='帀' then 'Z' 	 when @c_CN>='丫' and @c_CN<'帀' then 'Y'	 when @c_CN>='夕' and @c_CN<'丫' then 'X'	 when @c_CN>='屲' and @c_CN<'夕' then 'W'

		 when @c_CN>='他' and @c_CN<'屲' then 'T'	 when @c_CN>='仨' and @c_CN<'他' then 'S'	 when @c_CN>='呥' and @c_CN<'仨' then 'R'	 when @c_CN>='七' and @c_CN<'呥' then 'Q'

		 when @c_CN>='妑' and @c_CN<'七' then 'P'	 when @c_CN>='噢' and @c_CN<'妑' then 'O'	 when @c_CN>='拏' and @c_CN<'噢' then 'N'	 when @c_CN>='嘸' and @c_CN<'拏' then 'M'

		 when @c_CN>='垃' and @c_CN<'嘸' then 'L'	 when @c_CN>='咔' and @c_CN<'垃' then 'K'	 when @c_CN>='丌' and @c_CN<'咔' then 'J'	 when @c_CN>='铪' and @c_CN<'丌' then 'H'

		 when @c_CN>='旮' and @c_CN<'铪' then 'G'	 when @c_CN>='发' and @c_CN<'旮' then 'F'	 when @c_CN>='妸' and @c_CN<'发' then 'E'	 when @c_CN>='咑' and @c_CN<'妸' then 'D'

		 when @c_CN>='嚓' and @c_CN<'咑' then 'C'	 when @c_CN>='八' and @c_CN<'嚓' then 'B'	 when @c_CN>='吖' and @c_CN<'八' then 'A'	 Else '' End

--   	select  top 1 @c = Code  from Code_PY   where name <= @c_CN

  end

  else

  begin

	--过滤字符（除了字符、数字和'.'外的所有字符）

	if (@c_CN>='a' or (@c_CN>='0' and @c_CN<='9') or @c_CN='.')

		set @c=@c_CN

	else

		set @c=''

  end

  

  set @c_Return=@c_Return+isnull(@c ,'')

 end

 return  upper(@c_Return)

end

-晴天 2011-03-12

打赏
举报

你是想直接翻译?那得有一个字典表才行.或者,找出文字排列顺序中ABCD...首字母的位置,再插进去排列.

有事儿去了,不能详细讨论.

-晴天 2011-03-12

打赏
举报

create table tb(公司名称 nvarchar(10),英文名 varchar(10))

insert into tb(公司名称) select '做创维科技'

go

create function f_GetPy(@str nvarchar(4000))

returns nvarchar(4000)

as

begin

declare @i int

declare @t table(id int,chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))

insert into @t

  select 1,'吖','A' union all select 2,'八','B' union all

  select 3,'嚓','C' union all select 4,'咑','D' union all

  select 5,'妸','E' union all select 6,'发','F' union all

  select 7,'旮','G' union all select 8,'铪','H' union all

  select 9,'丌','J' union all select 10,'咔','K' union all

  select 11,'垃','L' union all select 12,'嘸','M' union all

  select 13,'拏','N' union all select 14,'噢','O' union all

  select 15,'妑','P' union all select 16,'七','Q' union all

  select 17,'呥','R' union all select 18,'仨','S' union all

  select 19,'他','T' union all select 20,'屲','W' union all

  select 21,'夕','X' union all select 22,'丫','Y' union all

  select 23,'帀','Z' union all select 24,nchar(1617),''

  set @i=1

  while @i<=len(@str)

	begin

	select @str=stuff(@str,@i,1,a.letter)

		from @t a inner join @t b on a.id=b.id-1

		where substring(@str,@i,1)>=a.chr and substring(@str,@i,1)<b.chr

	set @i=@i+1

    end

  return(@str)

end

go

update tb set [英文名]=dbo.f_GetPy([公司名称])

select * from tb

/*

公司名称       英文名

---------- ----------

创维科技       CWKJ



(1 行受影响)



*/

go

drop table tb

drop function dbo.f_GetPy