每一行split分割, 然后发现了cm即打印 dim str() as string , arr() as string redim str(2) str(0) = "1438-2907页;29cm;精装" str(1) = "419页;26cm" str(2) = "228页:图;26cm;精装" dim i as long , j as long for i = 0 to ubound(str) arr = split(str(i), ";") for j = 0 to ubound(arr) if right(arr(j),2) = "cm" then debug.print i,arr(j) exit for endif next j next i
dim str() as string , arr() as string
redim str(2)
str(0) = "1438-2907页;29cm;精装"
str(1) = "419页;26cm"
str(2) = "228页:图;26cm;精装"
dim i as long , j as long for i = 0 to ubound(str)
arr = split(str(i), ";")
for j = 0 to ubound(arr)
if right(arr(j),2) = "cm" then
debug.print i,arr(j)
exit for
endif
next j
next i
这样得记录有1万多条,不可能用数组吧
这里关键事子串出现得位置不同 ,我事想再sql里取出子串付给另一个字段
谢谢
放到查询分析器中执行 CREATE TABLE T1 (
[字段1] [nvarchar] (100),
[字段2] [nvarchar] (100)
)
INSERT INTO T1 SELECT '1438-2907页;29cm;精装',''
UNION ALL SELECT '419页;26cm',''
UNION ALL SELECT '228页:图;26cm;精装',''
UNION ALL SELECT '',''select * from t1
update t1 set 字段2=
substring(substring(字段1, 1, CHARINDEX ( 'cm' , 字段1)-1),
len((substring(字段1, 1, CHARINDEX ( 'cm' , 字段1)-1)))-
charindex (';', REVERSE (substring(字段1, 1, CHARINDEX ( 'cm' , 字段1)-1)))+2,
len(substring(字段1, 1, CHARINDEX ( 'cm' , 字段1)-1))-
(len((substring(字段1, 1, CHARINDEX ( 'cm' , 字段1)-1)))-
charindex (';', REVERSE (substring(字段1, 1, CHARINDEX ( 'cm' , 字段1)-1)))+2)+1
)where CHARINDEX ( 'cm' , 字段1) > 0select * from t1