0

This pattern works as expected. I want to improve it by:

  • Merging the repeating patterns : [0-9][0-9][0-9] into one. I tried [0-9{3}], no success.

  • Specify the special character - instead of [^0-9A-Z] 

SELECT PATINDEX('[1-2][0-9][0-9][0-9][^0-9A-Z][0-1][0-2][^0-9A-Z][0-3][0-9]', '2014-10-28');
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Rick
  • 55
  • 1
  • 12
  • I looks like you are trying to find valid dates. Why not try_convert(date,YourCol) is not null – John Cappelletti Oct 04 '19 at 17:49
  • If you want to stick with the patindex, you can trim it a bit SELECT PATINDEX('[1-2][0-9][0-9][0-9]-[0-1][0-2]-[0-3][0-9]', '2014-10-28'); – John Cappelletti Oct 04 '19 at 17:52
  • I need to stick with PATINDEX since I will be expanding this pattern to include time with milliseconds.@JohnCappelletti – Rick Oct 04 '19 at 18:02
  • In that case, rather than [^0-9A-Z] you can list the possible characters as such [-./] but it really won't buy you much (if anything) – John Cappelletti Oct 04 '19 at 18:08
  • To include time just use try_convert with the datetime datatype. That seems a lot easier. Then if at all possible fix the table so you stop storing dates as strings. – Sean Lange Oct 04 '19 at 18:30

0 Answers0