Extracting sub-string

Hi,
I have got data in a field in the following formats:
9999999_x
9999999_x_
9999999_xx
9999999_xx_
9999999_xxx
9999999_xxx_
9999999_xxxx
9999999_xxxx_
How can i extract the value for 'x' or 'xx' or 'xxx' or 'xxxx' using a
single query, where x can be any integer?
Thanks
Regards,
G
On Thu, 10 Mar 2005 18:47:02 -0800, gavin wrote:

>I have got data in a field in the following formats:
>9999999_x
>9999999_x_
>9999999_xx
>9999999_xx_
>9999999_xxx
>9999999_xxx_
>9999999_xxxx
>9999999_xxxx_
>How can i extract the value for 'x' or 'xx' or 'xxx' or 'xxxx' using a
>single query, where x can be any integer?
Hi G,
I do hope that this is a one-time cleanup operation for imported data.
Here's a query that will do it, assuming the data is in a table called
MyTable, in a varchar column called MyColumn and that there is no data
where the xxx part is non-numeric:
SELECT CAST(SUBSTRING(YourColumn, 9,
LEN(YourColumn) - CASE WHEN RIGHT(YourColumn, 1) = '_'
THEN 9 ELSE 8 END) AS int)
FROM MyTable
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)