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

/*
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

Rate

5 (1)

Share

Share

Rate

5 (1)