Baro
Baro

Reputation: 5520

How can I get this pattern using PATINDEX?

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:

Upvotes: 0

Views: 242

Answers (0)

Related Questions