如何用SQL实现带分隔符的不规则编码分列

xmirage

KMNO	KM1	KM2	KM3	KM4	KM5	KM6
411-02-43-B2-3-1	411	02	43	B2	3	1

编码如:411-02-43-B2-3-1,有分隔符"-"连接,如何用SQL语句(不用VBA)实现分列.效果如表格所示.

****************************************
补充问题:
能否按照每个KM(1-6)列的最大长度规范化,322222，不足位为填"0".再添加一列汇总各个KM列.如4110243B20301

[ 本帖最后由 xmirage 于 2011-1-30 10:10 编辑 ]

zez

SELECT ID.[KMNO], IIf(InStr(1,Replace([KMNO] & "-","-","@",1,0),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,0),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,0),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,0),"@")-1)) AS 表达式1, IIf(InStr(1,Replace([KMNO] & "-","-","@",1,1),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,1),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,1),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,1),"@")-1)) AS 表达式2, IIf(InStr(1,Replace([KMNO] & "-","-","@",1,2),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,2),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,2),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,2),"@")-1)) AS 表达式3, IIf(InStr(1,Replace([KMNO] & "-","-","@",1,3),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,3),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,3),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,3),"@")-1)) AS 表达式4, IIf(InStr(1,Replace([KMNO] & "-","-","@",1,4),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,4),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,4),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,4),"@")-1)) AS 表达式5, IIf(InStr(1,Replace([KMNO] & "-","-","@",1,5),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,5),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,5),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,5),"@")-1)) AS 表达式6
FROM ID;

xmirage

非常感谢,困扰多日的问题终于解决了.

xmirage

补充问题:
能否按照每个KM(1-6)列的最大长度规范化,322222，不足位为填"0".再添加一列汇总各个KM列.如4110243B20301
下面的语句是要事先知道每列的最大长度,有没有办法在语句中加入判断每列的最大长度的语句.
SELECT ID.[KMNO],
IIf(InStr(1,Replace([KMNO] & "-","-","@",1,0),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,0),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,0),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,0),"@")-1)) AS KM1,
IIf(InStr(1,Replace([KMNO] & "-","-","@",1,1),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,1),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,1),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,1),"@")-1)) AS KM2,
IIf(InStr(1,Replace([KMNO] & "-","-","@",1,2),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,2),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,2),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,2),"@")-1)) AS KM3,
IIf(InStr(1,Replace([KMNO] & "-","-","@",1,3),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,3),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,3),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,3),"@")-1)) AS KM4,
IIf(InStr(1,Replace([KMNO] & "-","-","@",1,4),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,4),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,4),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,4),"@")-1)) AS KM5,
IIf(InStr(1,Replace([KMNO] & "-","-","@",1,5),"-")=0,"",Mid([KMNO],InStrRev(Replace([KMNO] & "-","-","@",1,5),"@")+1,InStr(1,Replace([KMNO] & "-","-","@",1,5),"-")-InStrRev(Replace([KMNO] & "-","-","@",1,5),"@")-1)) AS KM6,
Replace(Format([KM1],"@@@")," ","0") & Replace(Format([KM2],"@@")," ","0") & Replace(Format([KM3],"@@")," ","0") & Replace(Format([KM4],"@@")," ","0") & Replace(Format([KM5],"@@")," ","0") & Replace(Format([KM6],"@@")," ","0")
FROM ID;

[ 本帖最后由 xmirage 于 2011-1-30 10:43 编辑 ]