Skip to content

getting the second occurrence of a substring with PatIndex

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 



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


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

6 People found this is helpful