数据库中的数据是这样的
a
100
100.01
100.02
100.01.01
100.01.02
100.01.03
...
想要的显示结果是
a
100
100
100
100.01
100.01
100.01
...
也就是去掉最后一个.和他后边的数 通过一个函数生成一个这样的新视图最好 这个函数怎么写呢?下边是拿asp写的 不知道怎么改成sql的
if instr(str,".")<>0 then
dim strarr
strarr=split(str,".")
str=left(str,len(str)-len(strarr(ubound(strarr))))
else
str=str
end if
CSDN论坛浏览器:浏览、发帖、回复、结贴自动平均给分,下载地址:http://CoolSlob.ys168.com
a
100
100.01
100.02
100.01.01
100.01.02
100.01.03
...
想要的显示结果是
a
100
100
100
100.01
100.01
100.01
...
也就是去掉最后一个.和他后边的数 通过一个函数生成一个这样的新视图最好 这个函数怎么写呢?下边是拿asp写的 不知道怎么改成sql的
if instr(str,".")<>0 then
dim strarr
strarr=split(str,".")
str=left(str,len(str)-len(strarr(ubound(strarr))))
else
str=str
end if
CSDN论坛浏览器:浏览、发帖、回复、结贴自动平均给分,下载地址:http://CoolSlob.ys168.com
insert @t
select 'a'
union all select '100'
union all select '100.01'
union all select '100.02'
union all select '100.01.01'
union all select '100.01.02'
union all select '100.01.03'
select case when charindex('.',n)>0 then
reverse(stuff(reverse(n),1,charindex('.',reverse(n)),''))
else
n
end
from @t
union all select '100'
union all select '100.01'
union all select '100.02'
union all select '100.01.01'
union all select '100.01.02'
也就是想把a这个字段 所有数据都转了 存到一个新视图里
insert test
select 'a' union all
select '100' union all
select '100.01' union all
select '100.02' union all
select '100.01.01' union all
select '100.01.02' union all
select '100.01.03'select * from test
select left(col,len(col)-charindex('.',reverse(col))) from testdrop table test
union all
select reverse(stuff(reverse(a),1,charindex('.',reverse(a)),'')) from 表 where charindex('.',a)>0
create table test(col varchar(10))
insert test
select 'a' union all
select '100' union all
select '100.01' union all
select '100.02' union all
select '100.01.01' union all
select '100.01.02' union all
select '100.01.03'
select * from test
go-- 建函数
create function F_R(@Str varchar(100))
returns varchar(100)
as
begin
declare @r varchar(100)
select @r=left(@str,len(@str)-charindex('.',reverse(@str)))
return @r
end
goselect dbo.F_R(col) from testdrop function F_R
drop table test
(
@char char(1),
@str varchar(255)
)
returns varchar(255) as
beginif charindex(@char,@str)>0
select @str=left(@str,len(@str)-charindex(@char,reverse(@str)))
return @str
end
(
number varchar(100)
)
insert test
select '100' union all
select '100.01' union all
select '100.02' union all
select '100.01.01' union all
select '100.01.02' union all
select '100.01.03'
select * from test
go
create function gaibian
(
@char char(1),
@str varchar(255)
)
returns varchar(255) as
begin
if charindex(@char,@str)>0
select @str=left(@str,len(@str)-charindex(@char,reverse(@str)))
return @str
end
go
create view tview as
select dbo.gaibian('.',number) as aaa from test
go
select * from tview
----------------------------------------------------------------------------------
SQL语句中已经单独处理了,如下:select a from 表 where charindex('.',a)=0
union all
......
http://community.csdn.net/Expert/topic/4974/4974603.xml?temp=.6206171
这个帖子放在那里没人回啊,追加100分,放在这里怕被删了 -_-
col varchar(20)
)
insert bb values('100')
insert bb values('100.01')
insert bb values('100.01.01')
insert bb values('100.01.01.01')select case when charindex('.',col,charindex('.',col)+1)=0
then col
else subString(col,0,charindex('.',col,charindex('.',col)+1))
end
from bb
自己指定order by
datagrid导出excel
declare @t table( a varchar(100))
insert @t select '100'
union all select '100.01'
union all select '100.02'
union all select '100.01.01'
union all select '100.01.02'
union all select '100.01.03'select reverse(stuff(reverse(a),1,charindex('.',reverse(a)),'')) from @t结果:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100
100
100
100.01
100.01
100.01(所影响的行数为 6 行)
returns varchar(100)
as
begin
declare @r varchar(100)
select @r=left(@str,len(@str)-charindex('.',reverse(@str)))
return @r
end
go