sqlsubstringpatindex

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
Solution

  • 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