27,580
社区成员
发帖
与我相关
我的任务
分享字段拆分
将A字段拆分成A和B两个字段
A
ASDFD,34.342
AED,453.2
SFSFSD,234,45
A B
ASDFD 34.342
AED 453.2
SFSFSD 234,45
A
ASDFD,34.342
AED,453.2
SFSFSD,234,45
A B
ASDFD 34.342
AED 453.2
SFSFSD 234,45
...全文
请发表友善的回复…
发表回复
-狙击手- 2008-05-15
- 打赏
- 举报
/*
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/
-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(a varchar(20))
Go
Insert into Ta
select 'ASDFD,34.342' union all
select 'AED,453.2' union all
select 'SFSFSD,234,45' union all
select 'sdff,' union all
select 'sdfsdf,' union all
select 'sdf,'
Go
--Start
Select left(A,case when charindex(',',A)= 0 then len(a) else charindex(',',A)-1 end ) as A,
stuff(A,1,charindex(',',A),'') as B
from ta
--Result:
/*
A B
-------------------- -----------------------
ASDFD 34.342
AED 453.2
SFSFSD 234,45
sdff
sdfsdf
sdf
（所影响的行数为 6 行）
*/
--End
中国风 2008-05-15
- 打赏
- 举报
-->--> (Roy_88)测试数据#A表
if object_id('Tempdb..#A') is not null
drop table #A
Go
Create table #A(A nvarchar(100))
Go
insert #A select 'ASDFD,34.342 ' union all
select 'AED,453.2 ' union all
select 'SFSFSD,234,45 ' union all
select 'sdff, ' union all
select 'sdfsdf, ' union all
select 'sdf,'
select
case when stuff(A,1,patindex('%,%',A),'')>''
then left(A,patindex('%,%',A)-1)
else left(A,patindex('%,%',A)) end as A,
stuff(A,1,patindex('%,%',A),'') as B
from
#A
/*
A B
---------------------------------------------------------------------------------------------------- -------
ASDFD 34.342
AED 453.2
SFSFSD 234,45
sdff,
sdfsdf,
sdf,
（所影响的行数为 6 行）
*/-狙击手- 2008-05-15
- 打赏
- 举报
/*
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/
-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(a varchar(20))
Go
Insert into Ta
select 'ASDFD,34.342' union all
select 'AED,453.2' union all
select 'SFSFSD,234,45' union all
select 'sdff,' union all
select 'sdfsdf,' union all
select 'sdf,'
Go
--Start
Select left(A,case when charindex(',',A)= 0 then len(a) else charindex(',',A)-1 end ) as A,
rtrim(stuff(A,1,case when charindex(',',A)= 0 then len(a) else charindex(',',A) end,'')) as B
from ta
--Result:
/*
A B
-------------------- ---------------
ASDFD 34.342
AED 453.2
SFSFSD 234,45
sdff
sdfsdf
sdf
（所影响的行数为 6 行）
*/
--End
-狙击手- 2008-05-15
- 打赏
- 举报
/*
-- Author:Flystone
-- Date:2008-05-15
-- Version:V1.001
*/
-- Test Data: Ta
If object_id('Ta') is not null
Drop table Ta
Go
Create table Ta(a varchar(20))
Go
Insert into Ta
select 'ASDFD,34.342' union all
select 'AED,453.2' union all
select 'SFSFSD,234,45' union all
select 'sdff' union all
select 'sdfsdf' union all
select 'sdf'
Go
--Start
Select left(A,case when charindex(',',A)= 0 then len(a) else charindex(',',A)-1 end ) as A,
stuff(A,1,case when charindex(',',A)= 0 then len(a) else charindex(',',A) end,'') as B
from ta
--Result:
/*
A B
-------------------- ---------
ASDFD 34.342
AED 453.2
SFSFSD 234,45
sdff
sdfsdf
sdf
（所影响的行数为 6 行）
*/
--End
snow1103 2008-05-15
- 打赏
- 举报
或
原表的另一种情况,字母后无数字
ASDFD,34.342
AED,453.2
SFSFSD,234,45
sdff,
sdfsdf,
sdf,
原表的另一种情况,字母后无数字
ASDFD,34.342
AED,453.2
SFSFSD,234,45
sdff,
sdfsdf,
sdf,
snow1103 2008-05-15
- 打赏
- 举报
补充一下
原表的另一种情况,字母后无数字
ASDFD,34.342
AED,453.2
SFSFSD,234,45
sdff
sdfsdf
sdf
原表的另一种情况,字母后无数字
ASDFD,34.342
AED,453.2
SFSFSD,234,45
sdff
sdfsdf
sdf
snow1103 2008-05-15
- 打赏
- 举报
varchar 50
中国风 2008-05-15
- 打赏
- 举报
[Quote=引用 5 楼 snow1103 的回复:]
提示错误,传递到 SUBSTRING 函数的长度参数无效。
[/Quote]
字段类型是?
提示错误,传递到 SUBSTRING 函数的长度参数无效。
[/Quote]
字段类型是?
snow1103 2008-05-15
- 打赏
- 举报
提示错误,传递到 SUBSTRING 函数的长度参数无效。
-狙击手- 2008-05-15
- 打赏
- 举报
select A=left(A,charindex(',',A)-1), B=right(A,len(A)-charindex(',',A)) from #T
中国风 2008-05-15
- 打赏
- 举报
select
left(A,patindex(',',A)-1) as A,
stuff(A,1,patindex(',',A),'') as B
from
T中国风 2008-05-15
- 打赏
- 举报
select
left(A,patindex(',',A)-1) as A,
stuff(A,1,patindex(',',A) as B
from
T
Limpire 2008-05-15
- 打赏
- 举报
--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A varchar(13))
insert into #T
select 'ASDFD,34.342' union all
select 'AED,453.2' union all
select 'SFSFSD,234,45'
select A=left(A,charindex(',',A)-1), B=right(A,len(A)-charindex(',',A)) from #T
/*
A B
------------- -------------
ASDFD 34.342
AED 453.2
SFSFSD 234,45
*/
