SQL Query for CR/LF - 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)

SQL Query for CR/LF

Author

Topic

Bernard_za
Starting Member

6 Posts

Posted - 2003-07-08 : 06:07:20

I have a table that contains all Company details including the Address Details. Unfortunately these address lines also include Carraige Return/Line Feed characters.

The following code removes the CR/LF characters:
------------------------------------------------
Addr= '' + replace(replace(t2.[ADDR],char(10),' '),char(13),' ')+ ' ',

Before = 23 Oakdale RoadDurbanville
After = 23 Oakdale Road Durbanville

I need some code that will allow the values after the CR/LF character to be made part of another column. In other words the 23 Oakdale Road will be "Address line 1" while the Durbanville will be "Address line 2"

Please help!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-08 : 06:45:02

Assuming you have a table with the following structure :

[Full Address][Line1][Line2][Line3][Line4][Line5]

[Full Address] sample Record

45, Saint Marks Avenue, Guildford, Surrey, KT15 5BN


DECLARE @A SMALLINT,@B SMALLINT,@C SMALLINT,@D SMALLINT,@E SMALLINT
UPDATE AddressTable
SET	@A = 1,
	LINE1 = SUBSTRING(ADDRESS,@A,CHARINDEX(',',ADDRESS)-1),
	@B = NULLIF(CHARINDEX(',',ADDRESS),0),
	LINE2 = SUBSTRING(ADDRESS,@B+1,ISNULL(NULLIF(CHARINDEX(',',ADDRESS,@B+1),0)-@B-1,LEN(ADDRESS)-@B)),
	@C = NULLIF(CHARINDEX(',',ADDRESS,@B+1),0),
	LINE3 = SUBSTRING(ADDRESS,@C+1,ISNULL(NULLIF(CHARINDEX(',',ADDRESS,@C+1),0)-@C-1,LEN(ADDRESS)-@C)),
	@D = NULLIF(CHARINDEX(',',ADDRESS,@C+1),0),
	LINE4 = SUBSTRING(ADDRESS,@D+1,ISNULL(NULLIF(CHARINDEX(',',ADDRESS,@D+1),0)-@D-1,LEN(ADDRESS)-@D)),
	@E = NULLIF(CHARINDEX(',',ADDRESS,@D+1),0),
	LINE5 = SUBSTRING(ADDRESS,@E+1,LEN(ADDRESS)-@E)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-08 : 06:46:06

Something like that:

select
left(addr,charindex(char(10),addr,1)) as Line1,
right(addr,len(addr)-charindex(char(10),addr,1)) as Line2

HTH

- Vit

Bernard_za
Starting Member

6 Posts

Posted - 2003-07-08 : 09:56:47

thanks to all of you, both solutions work like a dream!

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-08 : 10:05:26

quote:

Something like that:

select
left(addr,charindex(char(10),addr,1)) as Line1,
right(addr,len(addr)-charindex(char(10),addr,1)) as Line2

HTH

- Vit

What about line3, line4 etc...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources