0

I am trying to capture a specific sequence of characters: number (1 or two digit), space, minus, space, number (1 or two digit) but I can have some text before and/or after the sequence.

Examples:

abc 2 - 5
abc 5 - 10 def
abc 15 - 25
abc 15 - 25 def

First attempt

I tried this, but the pipe doesn't behave as I expected (like an OR)

PATINDEX('%[0-9|0-90-9] - [0-9|0-90-9]%', MyField)

The results are:

PATINDEX('%[0-9|0-90-9] - [0-9|0-90-9]%', 'abc 2 - 5')
    5
PATINDEX('%[0-9|0-90-9] - [0-9|0-90-9]%', 'abc 5 - 10 def'),
    5
PATINDEX('%[0-9|0-90-9] - [0-9|0-90-9]%', 'abc 15 - 25'),
    6
PATINDEX('%[0-9|0-90-9] - [0-9|0-90-9]%', 'abc 15 - 25 def')
    6

The function retrieves the second digit of the first number, which might be formally correct, but that's not what I want.

Second attempt

The second attempt seemed (to me!) more correct in terms of logic. Take the first number that is not preceded by a number (and reverse logic for the second number) with a space, minus, space in the middle.

PATINDEX('%[^0-9][0-9] - [0-9][^0-9]%', 'abc 2 - 5'),
PATINDEX('%[^0-9][0-9] - [0-9][^0-9]%', 'abc 5 - 10 def'),
PATINDEX('%[^0-9][0-9] - [0-9][^0-9]%', 'abc 15 - 25'),
PATINDEX('%[^0-9][0-9] - [0-9][^0-9]%', 'abc 15 - 25 def')

But it doesn't work that way either, all four give me index 0.

The questions are:

  • Does the pipe work as an OR ? (although it does not fit my purpose in this problem)
  • How can I get this pattern ?
Thom A
  • 88,727
  • 11
  • 45
  • 75
Baro
  • 5,300
  • 2
  • 17
  • 39
  • `PATINDEX` doesn't support things like quantifiers or optional parts of the pattern. – Martin Smith Aug 02 '22 at 11:12
  • Just in case you aren't aware, SQL Server 2012 ran out *completely* out of support a few weeks ago; *hopefully* you have your upgrade path in hand. – Thom A Aug 02 '22 at 11:12
  • @Larnu You're right, it's an old software that runs on this version of sql server, it needs a general update. – Baro Aug 02 '22 at 12:39
  • @Iptr It is a good starting point, even if in the real project I wanted to create a more generic function (similar to a regex, and therefore better understand my logical error in the tests above). But maybe the specific problem with your solution could be solved. When I get back to the office I will try it. – Baro Aug 02 '22 at 12:43

0 Answers0