Solved

To extract first particular pattern from irregular description.

Posted on 2008-06-16
3
190 Views
Last Modified: 2008-07-06
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:




0
Comment
Question by:Mehram
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 21791369
0
 

Author Comment

by:Mehram
ID: 21791448
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.
0
 

Author Comment

by:Mehram
ID: 21792497
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'









0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question