导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

如何把一个表中的列的文字转换一下

lsyyfj 2007-12-27 11:54:26
如一列的汉字
前片 --QP
后片 HP
...全文
64 点赞 收藏 11
写回复
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
dobear_0922 2007-12-27
update table
set col = case when col when '前片' then 'QP' when '后片' then 'HP' end
回复
lsyyfj 2007-12-27
我的意思就是说把一个表中的一个字段的汉字,都转换为拼音首个字母,能不能一次性都做好呀那个表有10000个记录
回复
dawugui 2007-12-27
在sql里面创建这个函数,然后调用它即可.
回复
lsyyfj 2007-12-27
潇洒老乌龟你这个如何用呀能说明一下
回复
dawugui 2007-12-27
应该是这个意思.
回复
tim_spac 2007-12-27
这个帖子好像应该放到"疑难问题"区 :)
回复
-狙击手- 2007-12-27
update table
set col = case when col = '前片' then '后片' else col end
回复
wzy_love_sly 2007-12-27
replace(列,'a','')
把列的字符‘a'换成'b'?
回复
dawugui 2007-12-27
--获取拼音首字母函数
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

回复
-狙击手- 2007-12-27
不懂
回复
dawugui 2007-12-27
--获取拼音首字母函数
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 行受影响)
*/
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告