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
I think this is one of the most significant info for me.
And i am glad reading your article. But want to remark on some
general things, The web site style is perfect, the articles
is really great : D. Good job, cheers
I am in fact thankful to the holder of this site who has shared this great piece of writing
at here.
Amazing! Its actually remarkable piece of information I have got much clear idea
about from how to extract the zip code using SQL
Good day! I know this is kinda off topic but I’d figured I’d
ask. Would you be interested in exchanging links or maybe guest authoring a blog
article or vice-versa? My site covers a lot of
the same subjects as yours and I think we could greatly benefit from
each other. If you might be interested feel free to send
me an email. I look forward to hearing from you! Superb blog by the way!
Hello! I could have sworn I’ve been to your blog before but after looking at a few of the articles I realized it’s new to me.
Anyways, I’m certainly delighted I discovered it
and I’ll be bookmarking it and checking back frequently!
Excellent post. I was checking continuously this weblog and I am
impressed! Extremely useful information specially the remaining
part 🙂 I was seeking this
certain information for a long time. Thanks and best of luck.
Good way of telling, and nice post to obtain information about my presentation subject, which i am going to present in university.
I love your blog.. very nice colors & theme.
Did you design this website yourself or did you hire someone to do
it for you? Plz respond as I’m looking to design my own blog and would like to know where u got this from.
thank you
HI, I did it myself. thanks for compliment