+012 617 9233 opaps.com@gmail.com

Many years ago, when I am not so proficient in SQL, I often use programming language with complex loop and later with REGEX to extract 5 digits zip code from database. But when the advancement in technology , things can be pretty much easy. Just use PATINDEX in SQL statement.

Patindex (pattern index) is a very powerful function extract string position.

update [TABLENAME] set postcode=

case when

patindex(‘%[0-9][0-9][0-9][0-9][0-9]%’,address)>0 then

substring(address,patindex(‘%[0-9][0-9][0-9][0-9][0-9]%’,address),5)

else ”

end WHERE len(postcode)=0

Explanation

patindex(‘%[0-9][0-9][0-9][0-9][0-9]%’,address)>0 then

Detect 5 digits code. If you need to detect more than 5 digits just add more [0-9].

substring(address,patindex(‘%[0-9][0-9][0-9][0-9][0-9]%’,address),5)

extract with patindex position, with length of 5

Hope this will help you guys