How do I get rid of .. Replace(Replace(Replace(Replace(Replace( … ?

Question

I’m selecting data on an old database which has an abused status column. The status column has multiple pieces of information in it. Values are like ‘New Contact YYYY’, ‘Online YYYY’, ‘Updated YYYY’, ‘Withdrawn YYYY’, etc…. As you may have guessed, YYYY represents the year … which I need.

In the past I’ve done something similar to

Rtrim( ltrim( Replace(Replace(Replace(Replace(Replace( …

Basically, replacing all text values with an empty string, so the only thing that still exists is the year. I can still do this, but I’m thinking this is ridiculous, and there’s got to be a better way.

Does anybody know of a better way to do this?

Answer 1

If you simply want to extract a four digit year from the string, you could use PATINDEX

SELECT SUBSTRING(FieldName, PATINDEX('%[0-9][0-9][0-9][0-9]%', FieldName), 4)
FROM TableName

Answer 2

If the year allways are the last 4:

SELECT right(FieldName,4) from table

Answer 3

All the answers given solve your problem, but the correct answer is to normalize your data base better. If you need the year as a separate item, it should be stored in a separate column.

Anything else is just removing hairs from a wart, as my dear old grandma used to say (she never actually said that, I just thought of it and it sounded kind of cool - must be why I don't get out much :-).