sql server mseq: 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,

[quoted text, click to view]

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
--