Reputation: 5520
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