Addled - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Addled

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-20 : 11:29:09

I'm trying to rearrange some address data. At the moment it is in a single field varchar(255) with some carriage returns in it (char(13)+ char(10))

This sequence of carriage returns can appear up to twice (if it appears more than that, everything after the third one can be ignored) I want this put into seperate fields. The code I have at the moment splits it into two fields but I am having trouble getting the third one.

SELECT  ADDRESS as ORIGINAL
, CASE WHEN CHARINDEX(Char(13)+ char(10), ADDRESS) = 0 THEN ADDRESS -- carriage sequence doesn't appear
WHEN CHARINDEX(Char(13)+ char(10), ADDRESS) is null THEN ADDRESS -- address field could be NULL!
ELSE LEFT(ADDRESS, (CHARINDEX( Char(13)+ char(10), ADDRESS))) 
END
as ADDRESS1,
CASE WHEN CHARINDEX(Char(13)+ char(10), ADDRESS) = 0 THEN NULL
WHEN CHARINDEX(Char(13)+ char(10), ADDRESS) is null THEN NULL
ELSE SUBSTRING(ADDRESS,(CHARINDEX( Char(13)+ char(10), ADDRESS))+2,LEN(ADDRESS)-CHARINDEX( Char(13)+ char(10), ADDRESS) ) 
END
as Address2

-- EVERYTHING UP TO HERE WORKS FINE

, CASE WHEN CHARINDEX(char(10)+Char(13), REVERSE(ADDRESS)) = 0 THEN NULL
WHEN CHARINDEX(char(10)+Char(13), REVERSE(ADDRESS)) is null THEN NULL
ELSE RIGHT(ADDRESS, (CHARINDEX(char(10)+Char(13), REVERSE(ADDRESS)))-1)
END
AS ADDRESS3
FROM PERSON

The last bit doesn't work as intended as if there is only one carriage return combo it returns the same as address2. In most cases Address3 won't exist but if it's possible I would like to extract it. If not I would just like address2 to be anything between the first and the second carriage return combo (if there is one)

Surely there must be an easier way!!

Thanks in advance

steve

To alcohol ! The cause of - and solution to - all of life's problems

Kristen
Test

22859 Posts

Posted - 2004-12-20 : 12:53:40

I normally do it with an intermediate table, and rely on the Left-to-Right processing logic. Something like this:


DECLARE @ColumnDelimiter varchar(10)
SELECT @ColumnDelimiter = ','

DECLARE @MyTable TABLE
(
	ID	int IDENTITY(1,1),
	Status	int,
	MyRow	varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
	P1	int,
	MyCol1	varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
	P2	int,
	MyCol2	varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
	P3	int,
	MyCol3	varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
	P4	int,
	MyCol4	varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS,
	P5	int,
	MyCol5	varchar(1000) COLLATE SQL_Latin1_General_CP1_CI_AS
)
	
-- Insert into temporary table
INSERT INTO @MyTable(MyRow)
	-- Add additional column delimiters to make sure there are enough!
SELECT	MyMasterColumn + REPLICATE(@ColumnDelimiter, 5)
FROM	dbo.MyMasterTable
	
DECLARE @p1 int,
	@p2 int,
	@p3 int,
	@p4 int,
	@p5 int
UPDATE @MyTable
SET 
	@p1 = CHARINDEX(@ColumnDelimiter, MyRow),
	MyCol1 = SUBSTRING(MyRow, 1, @p1-1),
	@p2 = CHARINDEX(@ColumnDelimiter, MyRow, @p1+1),
	MyCol2 = SUBSTRING(MyRow, @p1+1, @p2-@p1-1),
	@p3 = CHARINDEX(@ColumnDelimiter, MyRow, @p2+1),
	MyCol3 = SUBSTRING(MyRow, @p2+1, @p3-@p2-1),
	-- NOTE: This example assumes that MyCol4 & MyCol5 are optional
	@p4 = COALESCE(CHARINDEX(@ColumnDelimiter, MyRow, @p3+1), DATALENGTH(MyRow)),
	MyCol4 = SUBSTRING(MyRow, @p3+1, @p4-@p3-1),
	@p5 = COALESCE(CHARINDEX(@ColumnDelimiter, MyRow, @p4+1), DATALENGTH(MyRow)),
	MyCol5 = SUBSTRING(MyRow, @p4+1, @p5-@p4-1)
-- These may be useful for debugging
-- 	P1 = @p1,
-- 	P2 = @p2,
-- 	P3 = @p3,
-- 	P4 = @P4,
-- 	P5 = @P5
FROM	@MyTable

-- Now store results into "Output Table"

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-20 : 13:48:58

How bout this:


Declare @myVariable nvarchar(1000)

Set @myVariable = 'Seventhnight
123 Seventh St.
Greenville, SC 29607
ksjgds
sknbds'

Select @myVariable = case when (len(@myVariable)-len(replace(@myVariable,char(13)+char(10),'')))/2>3 then
	 left(@myVariable,charindex(char(13)+char(10),@myVariable,charindex(char(13)+char(10),@myVariable,charindex(char(13)+char(10),@myVariable)+1)+1))
	else @myVariable end

Select 
	Orig = @myVariable,
	Attn = Replace(parsename(Replace(Replace(@myVariable,'.','||'),char(13)+char(10),'.'),3),'||','.'),
	Street = Replace(parsename(Replace(Replace(@myVariable,'.','||'),char(13)+char(10),'.'),2),'||','.'),
	CityStateZip = Replace(parsename(Replace(Replace(@myVariable,'.','||'),char(13)+char(10),'.'),1),'||','.')

Corey

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-21 : 03:36:18

Thanks Kristen, Corey - I'll take a look at those

steve

To alcohol ! The cause of - and solution to - all of life's problems

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources