数据:CREATE TABLE ta(mb001 varchar(400))INSERT INTO ta
SELECT '定型套PE,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PE,(SDR9-SDR13.6,140,H62)' UNION
SELECT '定型套PE,(SDR17-SDR21,140,H62)' UNION
SELECT '定型套PE,(SDR22-SDR33,140,H62)' UNION
SELECT '定型套PE,(SDR41-SDR51,140,H62)' UNION
SELECT '定型套PP,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PP,(SDR9-SDR17.6,140,H62)' UNION
SELECT '定型套PP,(SDR26-SDR41,140,H62)'
原理:就是把140提到SDR前面去结果:
定型套PE,(140,SDR5-SDR7.4,H62)
定型套PE,(140,SDR9-SDR13.6,H62)
定型套PE,(140,SDR17-SDR21,H62)
....
SELECT '定型套PE,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PE,(SDR9-SDR13.6,140,H62)' UNION
SELECT '定型套PE,(SDR17-SDR21,140,H62)' UNION
SELECT '定型套PE,(SDR22-SDR33,140,H62)' UNION
SELECT '定型套PE,(SDR41-SDR51,140,H62)' UNION
SELECT '定型套PP,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PP,(SDR9-SDR17.6,140,H62)' UNION
SELECT '定型套PP,(SDR26-SDR41,140,H62)'
原理:就是把140提到SDR前面去结果:
定型套PE,(140,SDR5-SDR7.4,H62)
定型套PE,(140,SDR9-SDR13.6,H62)
定型套PE,(140,SDR17-SDR21,H62)
....
风哥,他数据里面有. 噢!patindex 找逗号?
CREATE TABLE ta(mb001 varchar(400))INSERT INTO ta
SELECT '定型套PE,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PE,(SDR9-SDR13.6,140,H62)' UNION
SELECT '定型套PE,(SDR17-SDR21,140,H62)' UNION
SELECT '定型套PE,(SDR22-SDR33,140,H62)' UNION
SELECT '定型套PE,(SDR41-SDR51,140,H62)' UNION
SELECT '定型套PP,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PP,(SDR9-SDR17.6,140,H62)' UNION
SELECT '定型套PP,(SDR26-SDR41,140,H62)'
goselect replace(replace(mb001,',(',',(140,'),',140,',',')
from tadrop table ta/*
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
定型套PE,(140,SDR17-SDR21,H62)
定型套PE,(140,SDR22-SDR33,H62)
定型套PE,(140,SDR41-SDR51,H62)
定型套PE,(140,SDR5-SDR7.4,H62)
定型套PE,(140,SDR9-SDR13.6,H62)
定型套PP,(140,SDR26-SDR41,H62)
定型套PP,(140,SDR5-SDR7.4,H62)
定型套PP,(140,SDR9-SDR17.6,H62)(8 行受影响)复杂了就取逗号的位置来编辑字符串了。
CREATE TABLE ta(mb001 varchar(400))INSERT INTO ta
SELECT '定型套PE,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PE,(SDR9-SDR13.6,140,H62)' UNION
SELECT '定型套PE,(SDR17-SDR21,140,H62)' UNION
SELECT '定型套PE,(SDR22-SDR33,140,H62)' UNION
SELECT '定型套PE,(SDR41-SDR51,140,H62)' UNION
SELECT '定型套PP,(SDR5-SDR7.4,140,H62)' UNION
SELECT '定型套PP,(SDR9-SDR17.6,140,H62)' UNION
SELECT '定型套PP,(SDR26-SDR41,140,H62)'
goselect * from ta;with cte as
(
select mb001,reverse(
substring(reverse(mb001),
charindex(',',reverse(mb001))+1,
charindex(',',reverse(mb001),charindex(',',reverse(mb001))+1) - charindex(',',reverse(mb001)) - 1)
)keyword
from ta
)select replace(replace(mb001,',(',',(' + keyword + ','),','+keyword+',',',')mb002
from ctedrop table ta/*mb001
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
定型套PE,(SDR17-SDR21,140,H62)
定型套PE,(SDR22-SDR33,140,H62)
定型套PE,(SDR41-SDR51,140,H62)
定型套PE,(SDR5-SDR7.4,140,H62)
定型套PE,(SDR9-SDR13.6,140,H62)
定型套PP,(SDR26-SDR41,140,H62)
定型套PP,(SDR5-SDR7.4,140,H62)
定型套PP,(SDR9-SDR17.6,140,H62)(8 行受影响)mb002
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
定型套PE,(140,SDR17-SDR21,H62)
定型套PE,(140,SDR22-SDR33,H62)
定型套PE,(140,SDR41-SDR51,H62)
定型套PE,(140,SDR5-SDR7.4,H62)
定型套PE,(140,SDR9-SDR13.6,H62)
定型套PP,(140,SDR26-SDR41,H62)
定型套PP,(140,SDR5-SDR7.4,H62)
定型套PP,(140,SDR9-SDR17.6,H62)(8 行受影响)
REPLACE(mb001+PARSENAME(mb002,2)+','+PARSENAME(mb002,3)+','+PARSENAME(mb002,1),'@','.') AS mb001
FROM (
SELECT mb001,mb002=REPLACE(REPLACE(mb002,'.','@'),',','.')
FROM
(SELECT mb002=STUFF(mb001,1,CHARINDEX('(',mb001),''),mb001=LEFT(mb001,CHARINDEX('(',mb001)) FROM ta)t
)t2