Parsing and trimming strings using SQL

[David Thomas]

We have a shop that has appended alpha characters to standard part codes.
We need to run a compare query between part codes in other tables and those
in this shop's table, but the alpha characters at the end of their codes
need to be removed before the codes can be compared. The codes vary in length
and some have internal alphas, but all codes properly end in a number. Do
you have a transact query for removing all the alpha characters at the end
of a string for each field in a column? We are using SQL Server 6.5.

[Rob Vieira]

The conditions you've specified remove a number of the first options I would
take. That said, can you count on the fact that their suffice will only
contain alphas? If so, I may be able to come up with something, though I
suspect it would be cursor based rather than set based (performance will,
therefore, likely stink).

One other though - is there a limited pattern to their suffixes. That is,
are there only a few suffixes that they use?

--
Rob Vieira
Visit www.ProfessionalSQL.com Today. It's new and improved!

"David Thomas" <david.thomas@pss.boeing.com> wrote in message
news:3a704e89@news.devx.com...
>
> We have a shop that has appended alpha characters to standard part codes.
> We need to run a compare query between part codes in other tables and
those
> in this shop's table, but the alpha characters at the end of their codes
> need to be removed before the codes can be compared. The codes vary in
length
> and some have internal alphas, but all codes properly end in a number. Do
> you have a transact query for removing all the alpha characters at the end
> of a string for each field in a column? We are using SQL Server 6.5.