m(Substring(name, Charindex('区', name) + 1, Len(name)))) AS name INTO #t7 FROM #t6 SELECT * FROM #t7 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 新城3075 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('城', name) + 1, Len(name)))) AS name INTO #t8 FROM #t7 SELECT * FROM #t8 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('沟', name) + 1, Len(name)))) AS name INTO #t9 FROM #t8 SELECT * FROM #t9 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN --无记录
数据库表进行关联，获得想要的数据。
SELECT * INTO #result FROM #t9 SELECT * FROM #result name 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 3069 3070 3071 3072 3073 3074 3075 3076 SELECT s.xxx, s.xxx FROM xx s JOIN #result r ON s.xxx = r.name WHERE s.xxx = 0;
四 总结本文过滤编号实际上核心代码就两个，第一个是利用SQL Server的内置函数过滤出指定编号，语句如下：
SELECT Rtrim(Ltrim(Substring(name, Charindex('店', name) + 1, Len(name)))) AS name INTO #t1 FROM #temp
SELECT * FROM #t1 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN
Good Luck!
|