-1

With this query I get the postal code from an address and it works, but there are some cases where the address number's length is 5 and so I get this instead of the postal code, is there any chance to get the last occurrence with PatIndex?

SELECT address,IIF((PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', address)>0), substring(address, PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', address), 5) , NULL) AS postalCode
from table
marko
  • 487
  • 1
  • 6
  • 15

1 Answers1

1

If you want to get the LAST occurrence

  1. Reverse address
  2. Find first match PatIndex in reversed address
  3. Reverse the first matching substring

Thus

SELECT address,IIF((PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', address)>0), reverse(substring(reverse(address), PatIndex('%[0-9][0-9][0-9][0-9][0-9]%', reverse(address)), 5)) , NULL) AS postalCode
from tbl

Run with db<>fiddle

shoek
  • 380
  • 2
  • 9