Skip to content
Advertisement

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 

Advertisement

Answer

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

6 People found this is helpful
Advertisement