刚学SQL,请大家帮我看看。
create table #a
(
fnumber varchar(255),
fshortNumber varchar(255)
)Insert into #a
Select 'A01' union all
Select 'A01.01' union all
Select 'A01.01.001' union all
Select 'A01.002' union all
Select 'A01.002.01.0004' 希望将fshortNumber 更新成为最后一个'.'后的值。没‘.’就等于fnumber列。谢谢大家!结果是select * from #a
A01 A01
A01.01 01
A01.01.001 001
create table #a
(
fnumber varchar(255),
fshortNumber varchar(255)
)Insert into #a
Select 'A01' union all
Select 'A01.01' union all
Select 'A01.01.001' union all
Select 'A01.002' union all
Select 'A01.002.01.0004' 希望将fshortNumber 更新成为最后一个'.'后的值。没‘.’就等于fnumber列。谢谢大家!结果是select * from #a
A01 A01
A01.01 01
A01.01.001 001
(
fnumber varchar(255),
fshortNumber varchar(255)
) Insert into #a(fnumber)
Select 'A01' union all
Select 'A01.01' union all
Select 'A01.01.001' union all
Select 'A01.002' union all
Select 'A01.002.01.0004' UPDATE #a SET
fshortNumber=RIGHT(fnumber,CHARINDEX('.',REVERSE('.'+fnumber))-1)select * from #adrop table #a
fshortNumber=RIGHT(fnumber,CHARINDEX('.',REVERSE('.'+fnumber))-1)select * from #a
create table #a
(
fnumber varchar(255),
fshortNumber varchar(255)
) Insert into #a
Select 'A01','' union all
Select 'A01.01','' union all
Select 'A01.01.001','' union all
Select 'A01.002','' union all
Select 'A01.002.01.0004',''
update #a set fshortNumber=(Case CharIndex('.',rTrim(fnumber)) When 0 then rTrim(fnumber) Else Right(rTrim(fnumber),CharIndex('.',Reverse(rTrim(fnumber)))-1) End)