34,576
社区成员
发帖
与我相关
我的任务
分享
declare @t table(p_code varchar(10),p_material varchar(20))
insert into @t select 'P4','M4,M5,M6'
insert into @t select 'P5','M1,M32,M27'
select
p_code,
cast(parseName(replace(p_material,',','.'),3) as varchar(10)) p_material1,
cast(parseName(replace(p_material,',','.'),2) as varchar(10)) p_material2,
cast(parseName(replace(p_material,',','.'),1) as varchar(10)) p_material3
from
@t
/*
p_code p_material1 p_material2 p_material3
---------- ----------- ----------- -----------
P4 M4 M5 M6
P5 M1 M32 M27
*/
declare @t table(p_code varchar(10),p_material varchar(20))
insert into @t select 'P4','M4,M5,M6'
insert into @t select 'P5','M1,M32,M27'
select
p_code,
parseName(replace(p_material,',','.'),3) as p_material1,
parseName(replace(p_material,',','.'),2) as p_material2,
parseName(replace(p_material,',','.'),1) as p_material3
from
@t
--> 测试时间:2009-07-08 22:34:12
--> 我的淘宝: http://shop36766744.taobao.com/
if object_id('[tab]') is not null drop table [tab]
create table [tab]([p_code] varchar(2),[p_material] varchar(10))
insert [tab]
select 'P4','M4,M5,M6' union all
select 'P5','M1,M32,M27'
select * from [tab]
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.p_code,p_material=substring(a.p_material,b.ID,charindex(',',a.p_material+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.p_material,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
/*
p_code p_material
------ ----------
P4 M4
P4 M5
P4 M6
P5 M1
P5 M32
P5 M27
(所影响的行数为 6 行)
*/
drop table tab,#Num
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(p_code varchar(10),p_material varchar(100))
go
insert into tb
select 'p4','M4,M5,M6' union all
select 'p5','M1,M32,M27'
go
SELECT TOP 1000 id = IDENTITY(int, 1, 1) INTO a FROM syscolumns a, syscolumns b
SELECT p_code, SUBSTRING(A.p_material, B.id, CHARINDEX(',', A.p_material + ',', B.id) - B.id) as value
FROM tb A, a B
WHERE SUBSTRING(',' + A.p_material, B.id, 1) = ','
order by p_code
/*------------
p4 M4
p4 M5
p4 M6
p5 M1
p5 M32
p5 M27
-------*/