asked on

To extract first particular pattern from irregular description.

SQL Server 2000

The function would be like
f_invoice_no ( @string varchar(1000)

@string
---------
Amount realzied dbldin & Sons $00444/07 9250 @60.79
Amount realzied dbldin & Sons $00444/07 9250 @60.79
Amount realzied dbldin & Sons $00444/07 9250 @60.79
Amount realzied dbldin & Sons $00444/07 9250 @60.79
Amount realzied dbldin & Sons $00444/07 9250 @60.79
Dc Purchased kmency garments inv#00507/07 #8752.37 @59.30
Dc Purchased kmency garments inv#00507/07 #8752.37 @59.30
Dc Purchased kmency garments inv#00507/07 #8752.37 @59.30
Dc Purchased kmency garments inv#00507/07 #8752.37 @59.30
Amount Realzied abcdef Ready made garments Inv#00536/07
Amount Realzied abcdef Ready made garments Inv#00536/07
Amount Realzied abcdef Ready made garments Inv#00536/07
AMount realzied xklo Corporation inv#00538/07, 00539/07
AMount realzied xklo Corporation inv#00538/07, 00539/07
AMount realzied xklo Corporation inv#00538/07, 00539/07
Dc Purchased xklo Corporation $101045.06 @60.62 inv#00188/08
Dc Purchased xklo Corporation $101045.06 @60.62 inv#00188/08
Dc Purchased xklo Corporation $101045.06 @60.62 inv#00188/08
Dc Purchased xklo Corporation $101045.06 @60.62 inv#00188/08
Dc Purchased xklo Corporation $101045.06 @60.62 inv#00188/08
Dc Purchased xklo Corporation $101045.06 @60.62 inv#00188/08

I want to extract invoice number of the pattern
[0-9][0-9][0-9][0-9][0-9][/][0-9][0-9]

In my sample data, for
Amount realzied dbldin & Sons $00444/07 9250 @60.79
I want 00444/07

If there are more than one available patterns , such as in my data,
AMount realzied xklo Corporation inv#00538/07, 00539/07
There are two 00538/07, 00539/07

Then
1) return all patttern
[00538/07,00539/07]
or return the exact text [MULTIPLE INVOICE]
whichever is easier for the expert, but it must not return only one pattern.

MTN:

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mehram

ASKER

http://www.sqlteam.com/article/regular-expressions-in-t-sql
The above is only telling whether pattern is found or not found, whereas I want the pattern to be returned.

Mehram

ASKER

I have reached so far

alter function f_FirstInvNoInVdDesc (
@string varchar(1000)
) returns varchar(1000)
AS
BEGIN

DECLARE @newstring varchar(1000)
declare @num int
select @newstring = ''
select @num = 1

while @num < len(@string)+1
begin
if Substring(@string, @num, 8) like '[0-9][0-9][0-9][0-9][0-9][/][0-9][0-9]'
or Substring(@string, @num, 8) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
begin
set @newstring = @newstring + Substring(@string, @num, 8)
set @num = 99999
end

set @num = @num + 1
end
RETURN @newstring
END

It is returning the first available pattern.
This is ok, if there is only one pattern,
but, this wrong when there are more than one pattern

select dbo.f_FirstInvNoInVdDesc ('Amount realzied dbldin & Sons $00444/07 9250 @60.79')
returns
00444/07 -- this is correct

select dbo.f_FirstInvNoInVdDesc ('AMount realzied xklo Corporation inv#00538/07, 00539/07')
returns
00538/07 -- this is incorrect because there is one more pattern 00539/07
In this case , I want this to return
'MULTIPLEINV'