--表包含条形码信息
create table FBarCode
(
FBarCode varchar(500)
)insert into FBarCode(FBarCode) values ('#GYS111#WL222#GGXH')
insert into FBarCode(FBarCode) values ('#GYSaa#WLbb#GGXHcc')
insert into FBarCode(FBarCode) values ('#GYSaaa#WLbbb#GGXH')
--条形码解析表
--FBillType表示类别
--FNumber表示条形码中的标识符号
--FName 表示含义
--FIndex 表示位置
CREATE TABLE [dbo].[barCode_Organize](
[FItemID] [int] IDENTITY(1,1) NOT NULL,
[FBillTypeID] [int] NULL,
[FNumber] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[FName] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[FIndex] [int] NULL,
[FDemo] [varchar](5000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]insert into barCode_Organize values(FBillTypeID,FNumber,FName,FIndex,FDemo) values(1,'#GYS','供应商',1,'')
insert into barCode_Organize values(FBillTypeID,FNumber,FName,FIndex,FDemo) values(1,'#WL','物料',2,'')
insert into barCode_Organize values(FBillTypeID,FNumber,FName,FIndex,FDemo) values(1,'#GGXH','规格型号',3,'')
请问怎样用Sql语句现实:
#GYS111#WL222#GGXH 表示成 供应商条码值111物料条码值222规格型号条码值
create table FBarCode
(
FBarCode varchar(500)
)insert into FBarCode(FBarCode) values ('#GYS111#WL222#GGXH')
insert into FBarCode(FBarCode) values ('#GYSaa#WLbb#GGXHcc')
insert into FBarCode(FBarCode) values ('#GYSaaa#WLbbb#GGXH')
--条形码解析表
--FBillType表示类别
--FNumber表示条形码中的标识符号
--FName 表示含义
--FIndex 表示位置
CREATE TABLE [dbo].[barCode_Organize](
[FItemID] [int] IDENTITY(1,1) NOT NULL,
[FBillTypeID] [int] NULL,
[FNumber] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[FName] [varchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[FIndex] [int] NULL,
[FDemo] [varchar](5000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]insert into barCode_Organize values(FBillTypeID,FNumber,FName,FIndex,FDemo) values(1,'#GYS','供应商',1,'')
insert into barCode_Organize values(FBillTypeID,FNumber,FName,FIndex,FDemo) values(1,'#WL','物料',2,'')
insert into barCode_Organize values(FBillTypeID,FNumber,FName,FIndex,FDemo) values(1,'#GGXH','规格型号',3,'')
请问怎样用Sql语句现实:
#GYS111#WL222#GGXH 表示成 供应商条码值111物料条码值222规格型号条码值
SELECT @I=MAX(LEN(FBarCode)-LEN(REPLACE(FBarCode,'#',''))) FROM FBarCode GROUP BY FBarCode
WHILE @I>0
BEGIN
UPDATE A SET A.FBarCode=REPLACE(A.FBarCode,B.FNumber,B.FName+'条码')
FROM FBarCode A
INNER JOIN barCode_Organize B
ON CHARINDEX(B.FNumber,A.FBarCode)>0
SET @I=@I-1
ENDSELECT * FROM FBarCode
/*
FBarCode
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
供应商条码111物料条码222规格型号条码
供应商条码aa物料条码bb规格型号条码cc
供应商条码aaa物料条码bbb规格型号条码(所影响的行数为 3 行)
*/