Looking for first 5 consecutive numbers in a string 2

[JeffMachine]

I have a text field that I need to search in looking for the first 5 consecutive numbers, which would represent a PO #.

Here is what some of the the data looks like, the numbers on the right is what I'd expect from the query:

Text field PO field

PO 31125 31125
PO 34698, 34699, 34713 & 34715 34698
50 % po35066 35066
full payment po 35360 35360
PO 33994 & PO 35522 33994
PO 36032 36032

As you can see, no consistancy in entering this data. I've added a PO field to the table but I would like to populate it with the Text data.

I have almost 800 records to fix. I realize I probably will have to manually adjust some records but I'd like to get as many as I could fixed via a update query.

Any help with the SQL statement would be much appreciated.

Thanks ...

 

[gmmastros]

You can use PatIndex to look for 5 consecutive numbers. Like this:

Declare @Temp Table(data VarChar(100))

Insert Into @Temp Values('PO 31125')
Insert Into @Temp Values('PO 34698, 34699, 34713 & 34715')
Insert Into @Temp Values('50 % po35066')
Insert Into @Temp Values('full payment po 35360')
Insert Into @Temp Values('PO 33994 & PO 35522')
Insert Into @Temp Values('PO 36032')

Select SubString(Data, PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', Data), 5)
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JeffMachine]

Thanks George, that worked great.

 

[Olaf Doschke]

A little caution, you should catch that you get Substr(date,0,5), that is the first five chars of the text field, if PatIndex does not find any PO in there.

...
Insert Into @Temp Values('3506 6')

Select
  Case
    When PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', Data)>0
    Then SubString(Data, PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', Data), 5)
    Else NULL
   End As PONumber
   From @Temp

Bye, Olaf.