sql server dts: Hi this is my first time here, so be patient with me :)
I'm looking for a way to parse addresses using sql
transact, to store as a DTS.
below are some examples: I would like to separate the
street name and street type in 2 diff. columns.
WILLOW CREEK PL
RED BARN DR
RED BARN DR
CARRINGTON DR
RENNER RD
EDMONTON CT
SPRINGBRANCH DR
HILLROSE DR
CEDAR RIDGE DR
LARTAN TRL
PRESIDENT GEORGE BUSH HWY

Tarig

OK so I am presuming that the PL,DR,CT,HWY is the type and the rest the
name?

What I would do is read the string from right to left and stop at the first
space. This is the TYPE
Now read from left to right for the length of the string - where you found
the space for TYPE and this is the NAME

so imagine @str is your field name

DECLARE @str varchar(50)
SET @str = 'Sunset BLVD'

select
LTRIM(RTRIM(LEFT(@str,len(@str) - CHARINDEX(' ',reverse(@str))))) as
[NAME],
LTRIM(RTRIM(RIGHT(@str,CHARINDEX(' ',reverse(@str))))) as [TYPE]







--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]