从一行字符串中提取特定字符串

本文介绍了从一行字符串中提取特定字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我有一张包含大量记录的表格。我在我的表格中有一个名为Narration的栏目。 在叙述栏中以数字形式提供的数据。在具有唯一代码的信号之间。 我必须从信号中提取该代码。 代码长度从15到15不等20. 代码以字母开头，结尾带数字 代码出现在任何部分的代码中 如何从报告中提取代码。 我的要求示例Im having a table with crores of records. Im having column called Narration in my table.In Narration column data in the form of sentance. In between the sentance Im having unique Code.I have to extract that code from the sentance.Code having varying length from 15 to 20.Code start with Letter and End with NumbersCode appears in any part of sentanceHow to extract code from the sentance.Example for my requirementNarration Codetwo coll-EntryNo-1-NIRMLTW02140010 NIRMLTW02140010EntryNo 54 For Amount towards & received for CDDHLTW1203280005 CDDHLTW1203280005CASH COLLECTION-EntryNo-1-PONAMPJ10210029 PONAMPJ10210029Being Charges Entry For Loan - RNGTAPJ1204020026 - RGT1274 RNGTAPJ1204020026amt rceived pack 20686-EntryNo-1-GNGE1PJ1203300016 GNGE1PJ1203300016 我必须提取这样的代码。I have to extract code like this.推荐答案DECLARE @tbl TABLE(Narration NVARCHAR(100))INSERT INTO @tbl (Narration)VALUES('two coll-EntryNo-1-NIRMLTW02140010')INSERT INTO @tbl (Narration)VALUES('EntryNo 54 For Amount towards & received for CDDHLTW1203280005')INSERT INTO @tbl (Narration)VALUES('CASH COLLECTION-EntryNo-1-PONAMPJ10210029')INSERT INTO @tbl (Narration)VALUES('Being Charges Entry For Loan - RNGTAPJ1204020026 - RGT1274')INSERT INTO @tbl (Narration)VALUES('amt rceived pack 20686-EntryNo-1-GNGE1PJ1203300016')DECLARE @tmp TABLE (Narration NVARCHAR(100), Position INT, Code NVARCHAR(100))DECLARE @pattern NVARCHAR(30)SET @pattern = '%[^0-9][A-Z][^-][^ ][0-9]%[^A-Za-z]%'INSERT INTO @tmp (Narration, Position, Code)SELECT Narration, Position, CodeFROM ( SELECT Narration, PATINDEX(@pattern, Narration) + 1 AS Position, SUBSTRING(Narration, PATINDEX(@pattern, Narration)+1, LEN(Narration) -PATINDEX(@pattern, Narration))AS Code FROM @tbl ) AS TSELECT *FROM @tmp 返回： RowNo 叙述 代码 1 amt rceived pack 20686-EntryNo-1-GNGE1PJ1203300016 No-1-GNGE1PJ1203300016 2 贷款收费 - RNGTAPJ1204020026 - RGT1274 - RGT1274 3 CASH COLLECTION-EntryNo-1-PONAMPJ10210029 No-1-PONAMPJ10210029 4 EntryNo 54 For Amount to&已收到CDDHLTW1203280005 o 54金额为&收到CDDHLTW1203280005 5 两个coll-EntryNo-1-NIRMLTW02140010 No-1-NIRMLTW02140010 不满意？不管。仍有希望和......解决方案。 ;） 之后你需要使用 CTE [ ^ ]以获得正确的代码。 这是一个例子：returns:RowNoNarrationCode1amt rceived pack 20686-EntryNo-1-GNGE1PJ1203300016No-1-GNGE1PJ12033000162Being Charges Entry For Loan - RNGTAPJ1204020026 - RGT1274 - RGT12743CASH COLLECTION-EntryNo-1-PONAMPJ10210029No-1-PONAMPJ102100294EntryNo 54 For Amount towards & received for CDDHLTW1203280005o 54 For Amount towards & received for CDDHLTW12032800055two coll-EntryNo-1-NIRMLTW02140010No-1-NIRMLTW02140010Not satisfying? No matter. There is still hope and... solution. ;)After that you need to parse '' '' (spaces) and ''-'' using CTE[^] to get correct code.Here is an example:CREATE TABLE #tmp (RowNo INT, Code NVARCHAR(100));WITH myCodes AS(SELECT RowNo, LTRIM(RTRIM(LEFT(Code, CHARINDEX(' ',Code)-1))) AS myCode,LTRIM(RTRIM(RIGHT(Code, LEN(Code) - CHARINDEX(' ',Code)))) AS RemainderFROM @tmpWHERE NOT Code IS NULL AND CHARINDEX(' ',Code)>0UNION ALLSELECT RowNo, LTRIM(RTRIM(LEFT(Remainder, CHARINDEX(' ',Remainder)-1))) AS myCode,LTRIM(RTRIM(RIGHT(Remainder, LEN(Remainder) - CHARINDEX(' ',Remainder)))) AS RemainderFROM myCodesWHERE NOT Remainder IS NULL AND CHARINDEX(' ',Remainder)>0UNION ALLSELECT RowNo, Remainder AS myCode, NULL AS RemainderFROM myCodesWHERE NOT Remainder IS NULL AND CHARINDEX(' ',Remainder)=0)INSERT INTO #tmp (RowNo, Code)SELECT RowNo, myCode AS CodeFROM myCodesWHERE LEN(myCode)>14 AND LEN(myCode)<21;WITH newCodes AS(SELECT RowNo, LTRIM(RTRIM(LEFT(Code, CHARINDEX('-',Code)-1))) AS myCode,LTRIM(RTRIM(RIGHT(Code, LEN(Code) - CHARINDEX('-',Code)))) AS RemainderFROM @tmpWHERE NOT Code IS NULL AND CHARINDEX('-',Code)>0UNION ALLSELECT RowNo, LTRIM(RTRIM(LEFT(Remainder, CHARINDEX('-',Remainder)-1))) AS myCode,LTRIM(RTRIM(RIGHT(Remainder, LEN(Remainder) - CHARINDEX('-',Remainder)))) AS RemainderFROM newCodesWHERE NOT Remainder IS NULL AND CHARINDEX('-',Remainder)>0UNION ALLSELECT RowNo, Remainder AS myCode, NULL AS RemainderFROM newCodesWHERE NOT Remainder IS NULL AND CHARINDEX('-',Remainder)=0)INSERT INTO #tmp (RowNo, Code)SELECT RowNo, myCode AS CodeFROM newCodesWHERE LEN(myCode)>14 AND LEN(myCode)<21SELECT *FROM #tmpORDER BY RowNoDROP TABLE #tmp 结果： RowNo 代码 1 GNGE1PJ1203300016 3 PONAMPJ10210029 4 CDDHLTW1203280005 5 NIRMLTW02140010 广告2） 正则表达式使模式匹配和数据提取更容易 [ ^ ] http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392 .aspx [ ^ ] xp_pcre - 正则表达式在T-SQL中 [ ^ ]results:RowNoCode1GNGE1PJ12033000163PONAMPJ102100294CDDHLTW12032800055NIRMLTW02140010Ad 2)Regular Expressions Make Pattern Matching And Data Extraction Easier[^]http://blogs.msdn.com/b/khen1234/archive/2005/05/11/416392.aspx[^]xp_pcre - Regular Expressions in T-SQL[^]declare @Str varchar(max)set @Str='two coll-EntryNo-1-NIRMLTW02140010 EntryNo 54 For Amount towards & received for CDDHLTW1203280005 CASH COLLECTION-EntryNo-1-PONAMPJ10210029 Being Charges Entry For Loan - RNGTAPJ1204020026 - RGT1274 amt rceived pack 20686-EntryNo-1-GNGE1PJ1203300016 'SELECT substring(substring(@Str, PATINDEX('%-1-%', @Str) ,40),4,15) as CodeSELECT substring(substring(@Str, PATINDEX('%For %', @Str) ,40),4,15) as CodeSELECT substring(substring(@Str, PATINDEX('% - %', @Str) ,40),3,15) as Code 或者您也可以从表中搜索：or you can Search from table also :SELECT substring(substring(narration, PATINDEX('%-1-%', narration) ,40),3,15) as CodeFROM TableNameWHERE PATINDEX('%-1-%', narration) <> 0union allSELECT substring(substring(narration, PATINDEX('%For %', narration) ,40),4,15) as CodeFROM TableNameWHERE PATINDEX('%For %', narration) <> 0union allSELECT substring(substring(narration, PATINDEX('% - %', narration) ,40),3,15) as CodeFROM TableNameWHERE PATINDEX('% - %', narration) <> 0 这篇关于从一行字符串中提取特定字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！