Hello,

I'm trying to parse for a numeric string from a column in a table. What
I'm looking for is a numeric string of a fixed length of 8.
The column is a comments field and can contain the numeric string in
any position. I'm not familiar with ActiveX, so any suggestions will be
useful.

Thanks in advance.

Is the source SQL Server?

CREATE TABLE Jumble(col1 varchar(20))
INSERT Jumble VALUES('12345678ABC')
INSERT Jumble VALUES('AAAAAAAAAAA')
INSERT Jumble VALUES('AB12345678C')


select * FROM dbo.Jumble
where Col1 LIKE ('%[0-9][0-9][0-9][0-9][0-9][0-9]%')







"SQL_developer" <vlavanya (AT) gmail (DOT) com> wrote

Thank you for the reply!
The source is SQL Server. The field I am trying to pull the data from
is a comments field.
Here's an example of the values in the column

1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-98
2) wed.kx10/26 Netrez 95860536

Now I need to parse through these lines and return only the 8 digit
numbers in it
The result set should be

27068935
95860536

OK

So what do you want to do with them?

You will need to write a function inside a DTS Active Script transform
that returns n instances of the 8 long integer

Do you want then to take in 1 row and write many ?

Allan

"SQL_developer" <vlavanya (AT) gmail (DOT) com> wrote

There is another column in the table for confirmation numbers. When the
user is not sure of the confirmation number, they type it in the
comments field. I need to look at the comments field, see if they have
typed in the confirmation number ( which is always 8 digits) and then
compare the string with all valid confirmation numbers in another
table. If the string matches with an existing confirmation number, I
need to update the first table with the string ( which is now a
validated confirmation number)

So in the above example if 95860536 is a valid confirmation number then
I need to update the field with that number.

Thank you for your valuable time!

Lavanya

Then you would pass the values found out to a lookup.

This would be very expensive though so I would look to do this something
like

Take the ID + number found out to a list
Check them in the lookup table and Update
Now update the main table

Something like that.

Allan


"SQL_developer" <vlavanya (AT) gmail (DOT) com> wrote

This is what I've done so far

Declare @tmp table
(
Booked_Comments_Txt varchar(255)
)

Insert into @tmp

select Comments_Txt from Reservation

select * FROM @tmp
where Comments_Txt LIKE ('%[0-9][0-9][0-9][0-9][0-9][0*9]%')

But it returns the entire comments field in the result set. What I need
is a way to return just those 8 digits.

It was being done manually, till now. But it's gotten to the point
where it's too big to handle. And that's why I'm trying to automate the
process.

Once I get the 8 digit number into my tmp table, I can easily compare
it and do the update process. But I can't seem to figure a way to get
the number alone......

Thanks

Lavanya

You need a recursive routine which looks into the string and outputs the
values found

This will have to be in a cursor as far as I can tell as well.


Look at the string
Find where your first [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]
starts
Retrieve the value.
Set your string now to start at one space after the previously returned
string a repeat until you encounter your pattern no more.


This is so much more suited to the CLR and will be really easy in SQL
Server 2005


Allan






"SQL_developer" <vlavanya (AT) gmail (DOT) com> wrote