0

Using SQL, is there an elegant way to remove the 'th' 'rd' and 'st' after a date? Here is how I'm doing it, but I'd rather do something like regex recall (example below).

Please no functions with while loops. I've already seen those solutions and I'm looking for something less ugly.

select Replace('september 8th, 2016', Substring('september 8th, 2016', PatIndex('%[a-z,A-Z][a-z,A-Z],%', 'september 8th, 2016'), 2), '')  statdate2

if using recall I could do something like below

/(\s[0-9]+)[a-z,A-Z]{2}\,/

and recall what the date number is with the replacement

 $1,

As-is my pattern inthe sql example may pull in any two characters followed by a comma which is good 99% of the time, but does match incorrectly some unwanted date misformats where users put the comma after the month.

Tom McDonald
  • 1,532
  • 2
  • 18
  • 37

1 Answers1

0

Well you could write a CLR function that uses regex recall.

By the way, your current query does a replace on the entire date string.

This becomes a problem if the suffix you want to replace is present in the month name.

For instance when I run your logic on August 1st, like this:

  select Replace('august 1st, 2016', Substring('august 1st, 2016', PatIndex('%[a-z,A-Z][a-z,A-Z],%', 'august 1st, 2016'), 2), '')  statdate2

I get:

augu 1, 2016

If you want a non-CLR solution that works, you probably need to eliminate the characters by their position in the string rather than using REPLACE(). Meaning get all the characters before the suffix, and concatenate it with all the characters after the suffix. It won't be pretty, but like I said, if you want pretty, you can go with CLR.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52