字符串截取

Question

sql

Answer 1

if object_id('[A]') is not null drop table [A]
create table A (  name varchar(200))
insert into A
select 'adfsda@@@@1@@0.002&&&1&&&aasdf@[email protected]&&&2&&&AAAA,BBB,VVVVV'  union all               
select 'asdfasdf@@@1@@@0.0020&1&asf@[email protected]&&&2&&&111111V'            union all               
select 'asdf@@@1@@@0.0020&&&1&&&@@@2@@@0.0000&&&2&&&222222223'         union all               
select 'asd@@@1@@@0.0020&&&1&&&@@@2@@@0.0123&&&2&&&3333333333333'      union all               
select 'as@@@1@@@0.0020&&&1&&&&@@@2@@@0.0000&&&2&&&4444444444'         union all               
select 'a@@@1@@@0.01320&&&1&&&asdf@@@2@@@@0.0000&&&2&&&555555'          
go
select '0.'+ substring( parsename(name,2),0,charindex('&',parsename(name,2))) as[Col1],'0.'+ substring( parsename(name,1),0,charindex('&',parsename(name,1))) as[Col2] from A      
--Col1 Col2
--0.002 0.0000
--0.0020 0.0000
--0.0020 0.0000
--0.0020 0.0123
--0.0020 0.0000
--0.01320 0.0000
                                                                                               
                                                                                               
                                                                                               
                                                                                               

Answer 2

为什么我返回的全部都是NULL

Answer 3

执行2楼代码没问题，这些就是你的真实数据？if object_id('[A]') is not null drop table [A]
create table A (  name varchar(200))
insert into A
select 'adfsda@@@@1@@0.002&&&1&&&aasdf@[email protected]&&&2&&&AAAA,BBB,VVVVV'  union all               
select 'asdfasdf@@@1@@@0.0020&1&asf@[email protected]&&&2&&&111111V'            union all               
select 'asdf@@@1@@@0.0020&&&1&&&@@@2@@@0.0000&&&2&&&222222223'         union all               
select 'asd@@@1@@@0.0020&&&1&&&@@@2@@@0.0123&&&2&&&3333333333333'      union all               
select 'as@@@1@@@0.0020&&&1&&&&@@@2@@@0.0000&&&2&&&4444444444'         union all               
select 'a@@@1@@@0.01320&&&1&&&asdf@@@2@@@@0.0000&&&2&&&555555'          
go
select '0.'+ substring( parsename(name,2),0,charindex('&',parsename(name,2))) as[Col1],
 
'0.'+ substring( parsename(name,1),0,charindex('&',parsename(name,1))) as[Col2] from A      /*
Col1                                                                                                                               Col2
---------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------
0.002                                                                                                                              0.0000
0.0020                                                                                                                             0.0000
0.0020                                                                                                                             0.0000
0.0020                                                                                                                             0.0123
0.0020                                                                                                                             0.0000
0.01320                                                                                                                            0.0000*/

Answer 4

try:
use tempdb
go
if object_id('[A]') is not null drop table [A]
create table A (  name varchar(200))
insert into A
select 'adfsda@@@@1@@0.002&&&1&&&aasdf@[email protected]&&&2&&&AAAA,BBB,VVVVV'  union all               
select 'asdfasdf@@@1@@@0.0020&1&asf@[email protected]&&&2&&&111111V'            union all               
select 'asdf@@@1@@@0.0020&&&1&&&@@@2@@@0.0000&&&2&&&222222223'         union all               
select 'asd@@@1@@@0.0020&&&1&&&@@@2@@@0.0123&&&2&&&3333333333333'      union all               
select 'as@@@1@@@0.0020&&&1&&&&@@@2@@@0.0000&&&2&&&4444444444'         union all               
select 'a@@@1@@@0.01320&&&1&&&asdf@@@2@@@@0.0000&&&2&&&555555'          
go
if object_id('fn_filter_Number') Is not null
Drop function fn_filter_Number 
Go
create function fn_filter_Number 
(
@str nvarchar(max)
)
returns @tb table(col1 nvarchar(50),col2 nvarchar(50))
as 
begin set @str='@'+@str+'@'
while(@str>'')
begin
if patindex('%0.%',@str)=0 break
set @str=stuff(@str,1,patindex('%0.%',@str)-1,'')

update @tb set col2=substring(@str,1,patindex('%[^.0-9]%',@str)-1)
if (@@rowcount =0)
insert into @tb ( col1 ) values(substring(@str,1,patindex('%[^.0-9]%',@str)-1))

set @str=stuff(@str,1,patindex('%[^.0-9]%',@str)-1,'')
end
return
end
go
select b.* 
from A 
cross apply dbo.fn_filter_Number(a.name) b/*
col1 col2
-------------------------
0.002 0.0000
0.0020 0.0000
0.0020 0.0000
0.0020 0.0123
0.0020 0.0000
0.01320 0.0000
*/