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

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?

From stackoverflow

• 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
  

  John MacIntyre : That's it. Thanks.

  Stefan : Nice! PATINDEX is new for me, Another day learning something new! ;)

  paxdiablo : I shudder when I see functions used to filter records as they're so inefficient. The schema should be modified to store year separately if you want to use it separately. This sort of thing is okay for small DBs but we work on truly huge mainframe DBs where this would be grounds for ridicule.

  Webjedi : This is a new one for me too...nice pick up...thanks!

• If the year allways are the last 4:

  SELECT right(FieldName,4) from table
  

  John MacIntyre : Not always, but thanks.

  Stefan : Luckilly Tim C had a better solution. ;)

• 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 :-).

  John MacIntyre : True but business people do this type of thing all the time. Rather than calling you, they just jam it in somewhere. This is a pretty mild issue, another client I'm at, it was part of the culture when I joined them ... just jam stuff in anywhere. I think I've finally convinced them to stop.

  paxdiablo : Luckily with mainframes, clients are charged for being inefficient (holding JDBC connections too long or using too much CPU time for queries). Like any bad dog, they need to be banged on the nose with a newspaper occasionally.