一 环境介绍
SQL Server
PRINT @@VERSION
MicrosoftSQLServer2012-11.0.2100.60(X64)
Feb10201219:39:15
Copyright(c)MicrosoftCorporation
EnterpriseEdition:Core-basedLicensing(64-bit)onWindowsNT6.1(Build7601:ServicePack1)
操作系统
------------------
System Information
------------------
Operating System: Windows 7 Ultimate 64-bit (6.1, Build 7601) Service Pack 1 (7601.win7sp1_gdr.130828-1532)
System Model: Aspire E1-471G
Processor: Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz (4 CPUs), ~2.6GHz
Memory: 4096MB RAM
二 实现功能从一大堆有包含中文字符和编号的字符串中过滤出编号。
三 实现模拟
CREATE TABLE #temp ( name VARCHAR(80) ); INSERT INTO #temp VALUES ('五道口店3059'); INSERT INTO #temp VALUES ('五羊?店3060'); INSERT INTO #temp VALUES ('杨家屯店3061'); INSERT INTO #temp VALUES ('十里堤店3062'); INSERT INTO #temp VALUES ('中关村店3063'); INSERT INTO #temp VALUES ('丽秀店3064'); INSERT INTO #temp VALUES ('石门店3065'); INSERT INTO #temp VALUES ('黄村店3066'); INSERT INTO #temp VALUES ('东圃店3067'); INSERT INTO #temp VALUES ('天河店3068'); INSERT INTO #temp VALUES ('人民路广场3069'); INSERT INTO #temp VALUES ('社区中心3070'); INSERT INTO #temp VALUES ('珠海市3071'); INSERT INTO #temp VALUES ('丽都3072'); INSERT INTO #temp VALUES ('晓月3073'); INSERT INTO #temp VALUES ('旧区3074'); INSERT INTO #temp VALUES ('新城3075'); INSERT INTO #temp VALUES ('水井沟3076');
SELECT Rtrim(Ltrim(Substring(name, Charindex('店', name) + 1, Len(name)))) AS name INTO #t1 FROM #temp
以下是这几个函数的使用说明：
Substring
Returns the part of a character expression that starts at the specified position and has the specified length. The position parameter and the length parameter must eva luate to integers.
Syntax
SUBSTRING(character_expression, position, length)
Arguments
character_expression
Is a character expression from which to extract characters.
position
Is an integer that specifies where the substring begins.
length
Is an integer that specifies the length of the substring as number of characters.
Result Types
DT_WSTR
Charindex
Syntax
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
Arguments
Return Types
Rtrim
Syntax
Arguments
Result Types
Ltrim
Syntax
Arguments
Result Types
SELECT * FROM #t1 3059 3060 3061 3062 3063 3064 3065 3066 3067 3068 人民路广场3069 社区中心3070 珠海市3071 丽都3072 晓月3073 旧区3074 新城3075 水井沟3076
SELECT * FROM #t1 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 人民路广场3069 社区中心3070 珠海市3071 丽都3072 晓月3073 旧区3074 新城3075 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('场', name) + 1, Len(name)))) AS name INTO #t2 FROM #t1 SELECT * FROM #t2 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 社区中心3070 珠海市3071 丽都3072 晓月3073 旧区3074 新城3075 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('心', name) + 1, Len(name)))) AS name INTO #t3 FROM #t2 SELECT * FROM #t3 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 珠海市3071 丽都3072 晓月3073 旧区3074 新城3075 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('市', name) + 1, Len(name)))) AS name INTO #t4 FROM #t3 SELECT * FROM #t4 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 丽都3072 晓月3073 旧区3074 新城3075 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('都', name) + 1, Len(name)))) AS name INTO #t5 FROM #t4 SELECT * FROM #t5 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 晓月3073 旧区3074 新城3075 水井沟3076 SELECT Rtrim(Ltrim(Substring(name, Charindex('月', name) + 1, Len(name)))) AS name INTO #t6 FROM #t5 SELECT * FROM #t6 WHERE name LIKE N'%[一-?]%' COLLATE Chinese_PRC_BIN 旧区3074 新城3075 水井沟3076 SELECT Rtrim(Ltri