Extract the specific string from a line of string

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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 Numbers
Code appears in any part of sentance

How to extract code from the sentance.

Example for my requirement

Narration                                                         Code

two coll-EntryNo-1-NIRMLTW02140010                                NIRMLTW02140010
EntryNo 54 For Amount towards & received for CDDHLTW1203280005    CDDHLTW1203280005
CASH COLLECTION-EntryNo-1-PONAMPJ10210029                         PONAMPJ10210029
Being Charges Entry For Loan - RNGTAPJ1204020026 - RGT1274        RNGTAPJ1204020026
amt rceived pack  20686-EntryNo-1-GNGE1PJ1203300016               GNGE1PJ1203300016

I have to extract code like this.

Posted 28-Mar-13 20:37pm

kirthiga S

Updated 28-Mar-13 20:39pm

v2

Add a Solution

Comments

Hemant Singh Rautela 29-Mar-13 3:39am

You have make some work yourself... I give you idea that ..
convert string in array of character then apply your filter conditions....using looping.

But 1 problem in your input string that your 'Code' not is a single word always. If it is alwlays single word (having space before & after) then it can make easy...

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-03-29T02:20:00

There are 2 possibilities:
1) Using PATINDEX() AND SUBSTRING() functions, but it's not optimized
2) Using Regular Expressions in T-SQL

Ad 1)
PATINDEX[^]
SUBSTRING[^]
Pattern Matching With SQL[^]

For example, below code:

SQL

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, Code
FROM (
    SELECT Narration, PATINDEX(@pattern, Narration) + 1 AS Position, SUBSTRING(Narration, PATINDEX(@pattern, Narration)+1, LEN(Narration) -PATINDEX(@pattern, Narration))AS Code
    FROM @tbl
    ) AS T

SELECT *
FROM @tmp

returns:

RowNo	Narration	Code
1	amt rceived pack 20686-EntryNo-1-GNGE1PJ1203300016	No-1-GNGE1PJ1203300016
2	Being Charges Entry For Loan - RNGTAPJ1204020026 - RGT1274	- RGT1274
3	CASH COLLECTION-EntryNo-1-PONAMPJ10210029	No-1-PONAMPJ10210029
4	EntryNo 54 For Amount towards & received for CDDHLTW1203280005	o 54 For Amount towards & received for CDDHLTW1203280005
5	two coll-EntryNo-1-NIRMLTW02140010	No-1-NIRMLTW02140010

Not 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:

SQL

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 Remainder
	FROM @tmp
	WHERE NOT Code IS NULL AND CHARINDEX(' ',Code)>0
	UNION ALL
	SELECT RowNo, LTRIM(RTRIM(LEFT(Remainder, CHARINDEX(' ',Remainder)-1))) AS myCode,
			LTRIM(RTRIM(RIGHT(Remainder, LEN(Remainder) - CHARINDEX(' ',Remainder)))) AS Remainder
	FROM myCodes
	WHERE NOT Remainder IS NULL AND CHARINDEX(' ',Remainder)>0
	UNION ALL
	SELECT RowNo, Remainder AS myCode, NULL AS Remainder
	FROM myCodes
	WHERE NOT Remainder IS NULL AND CHARINDEX(' ',Remainder)=0  
)
INSERT INTO #tmp (RowNo, Code)
SELECT RowNo, myCode AS Code
FROM myCodes
WHERE 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 Remainder
	FROM @tmp
	WHERE NOT Code IS NULL AND CHARINDEX('-',Code)>0
	UNION ALL
	SELECT RowNo, LTRIM(RTRIM(LEFT(Remainder, CHARINDEX('-',Remainder)-1))) AS myCode,
			LTRIM(RTRIM(RIGHT(Remainder, LEN(Remainder) - CHARINDEX('-',Remainder)))) AS Remainder
	FROM newCodes
	WHERE NOT Remainder IS NULL AND CHARINDEX('-',Remainder)>0
	UNION ALL
	SELECT RowNo, Remainder AS myCode, NULL AS Remainder
	FROM newCodes
	WHERE NOT Remainder IS NULL AND CHARINDEX('-',Remainder)=0  
)
INSERT INTO #tmp (RowNo, Code)
SELECT RowNo, myCode AS Code
FROM newCodes
WHERE LEN(myCode)>14 AND LEN(myCode)<21

SELECT *
FROM #tmp
ORDER BY RowNo

DROP TABLE #tmp

results:

RowNo	Code
1	GNGE1PJ1203300016
3	PONAMPJ10210029
4	CDDHLTW1203280005
5	NIRMLTW02140010

Ad 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[^]

Susheel Kumar Verma · Answer 2 · 2013-03-28T23:50:00

You can do similar like this :

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 Code
SELECT  substring(substring(@Str, PATINDEX('%For %', @Str) ,40),4,15) as Code
SELECT  substring(substring(@Str, PATINDEX('% - %', @Str) ,40),3,15) as Code

or you can Search from table also :

SQL

SELECT  substring(substring(narration, PATINDEX('%-1-%', narration) ,40),3,15) as Code
FROM TableName
WHERE PATINDEX('%-1-%', narration) <> 0
union all
SELECT  substring(substring(narration, PATINDEX('%For %', narration) ,40),4,15) as Code
FROM TableName
WHERE PATINDEX('%For %', narration) <> 0
union all
SELECT  substring(substring(narration, PATINDEX('% - %', narration) ,40),3,15) as Code
FROM TableName
WHERE PATINDEX('% - %', narration) <> 0

Extract the specific string from a line of string

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0