Script to match patterns thru reg. expressions

/*
Script for finding matching patterns using regular expressions syntax.

Created by Eva Zadoyen
ezadoyen@ssd.com
06/20/2002
*/
use pubs

create table #author_address
(ID  INT IDENTITY,
AUTHOR_CODE CHAR(15),
AUTHOR_NAME VARCHAR(50),
ADDRESS1 VARCHAR(50),
ADDRESS2 VARCHAR(50),
ADDRESS3 VARCHAR(50))

insert #author_address ( AUTHOR_CODE,AUTHOR_NAME ,ADDRESS1,ADDRESS2,ADDRESS3)
SELECT  AU_ID,AU_LNAME + ', ' + AU_FNAME,ADDRESS,CITY, STATE + '-'+ZIP
FROM authors

-- select * from #author_address


SELECT ID,AUTHOR_CODE,AUTHOR_NAME,
coalesce(case when  address1 like '%[0-9][0-9][0-9][0-9][0-9]' 
then right(rtrim(address1),5) end ,
case when  address2 like '%[0-9][0-9][0-9][0-9][0-9]'
then right(rtrim(address2),5) end,
case when  address3 like '%[0-9][0-9][0-9][0-9][0-9]' 
then right(rtrim(address3),5) end) ZIP
from  #author_address

order by ZIP


SELECT AUTHOR_CODE,AUTHOR_NAME,
coalesce(case when  address1 like '%[ ][A-z][A-z][.]%' 
then rtrim(address1) end ,
case when  address2 like '%[ ][A-z][A-z][.]%' 
then rtrim(address2) end,
case when  address3 like '%[ ][A-z][A-z][.]%' 
then rtrim(address3) end,address1) 'Street',
coalesce(case when  address1 like '%[ ][A-z][A-z][.]%' 
then SUBSTRING(address1,patindex('%[ ][A-z][A-z][.]%' ,address1)+1 ,3) end ,
case when  address1 like '%[ ][A-z][A-z][.]%' 
then SUBSTRING(address1,patindex('%[ ][A-z][A-z][.]%' ,address1)+1 ,3) end ,
case when  address1 like '%[ ][A-z][A-z][.]%' 
then SUBSTRING(address1,patindex('%[ ][A-z][A-z][.]%' ,address1)+1 ,3) end ,'')  STREET_ABBR

from  #author_address
order by STREET_ABBR
--drop table  #author_address