SELECT mc, dz, (substring(dz_gs,Cast((patindex('%路%',dz_gs)) as int)+1,
(Cast((patindex('%号%',dz_gs)) as int)-Cast((patindex('%路%',dz_gs)) as int)-1) ))
FROM dwjbxx where dz is not null and dz !=''
结果:
路75号1单元502室 75
路72号 72
SELECT mc, dz, (substring(dz_gs,Cast((patindex('%路%',dz_gs)) as int)+1,
(Cast((patindex('%号%',dz_gs)) as int)-Cast((patindex('%路%',dz_gs)) as int)-1) ))
FROM dwjbxx where dz is not null and dz !=''