Split string delimited on line breaks (ASCII Code 13).

[Felix18807]

Hi I need to write a query based on a table with a field called AddressDetail which contains a string which is broken on line breaks. I need to return these as AddressLine1, AddressLine2 etc. The difficulty I am having is that I do not have write access and cannot save functions.

Is there some way of getting the nth part of the delimiited string WITHOUT using a function?

Thanks

 

[Felix18807]

(I mean User Defined Function)

 

[simian336]

Could you provide some test data and desired result.

Simi

 

[Felix18807]

A table like this where the AddressString contains Carriage Return(CR) characters -

ID,REF,NAME,ADDRESSLINES
327,A000190,AOC Management Services,5th Floor Centre Point (CR)103 New Oxford Street
328,B000190,Bicton College of Agriculture,East Budleigh
330,B000390,Bolton College,Manchester Road
331,B000490,Brighton Hove & Sussex VIth Form College,Dyke Road
332,E000190,Edexcel,Stewart House(CR)32 Russell Square(CR)DavesTown

Split out the carriage returns into a new table -
ID,REF,NAME,ADDRESSLINES,ADDRESSLINE2,ADDRESSLINE3
327,A000190,AOC Management Services,5th Floor Centre Point,103 New Oxford Street,
328,B000190,Bicton College of Agriculture,East Budleigh,,
330,B000390,Bolton College,Manchester Road,,
331,B000490,Brighton Hove & Sussex VIth Form College,Dyke Road,,
332,E000190,Edexcel,Stewart House,32 Russell Square,DavesTown

Sorry I've resorted to showing the tables as comma delimited strings because I couldn't think of a better way to present them.

 

[djj55]

Try CHARINDEX(CHAR(13), column), or maybe use CHAR(10).
This will find the break, then use SUBSTRING.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Felix18807]

Having trouble with that approach past the second CR
The logic for AddressLine1 is fairly straight forward
If the count of carriage returns = 0
then AddressLine1 = AddressLines
Else
AddressLine1 = Left(addresslines, charindex(CR))

Next bit is more tricky but I can cope so far

if count of carriage returns = 2
then addressline2 = right(addresslines, length of addresslines -charindex(CR))

Now if count of CR >2 my brain explodes leaks out my nose and makes a mess on the carpet.

 

[Felix18807]

Ok part 1 of solution. Finding the comma locations..

--Comma Positions
SELECT CHARINDEX(CHAR(13),[ADDRESSDETAIL]) as Comma1
FROM dbo.tblClient

SELECT CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL])+1) as Comma2
FROM dbo.tblClient

SELECT CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL])+1)+1) as Comma3
FROM dbo.tblClient

SELECT CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL])+1)+1)+1) as Comma4
FROM dbo.tblClient

SELECT CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL],CHARINDEX(CHAR(13),[ADDRESSDETAIL])+1)+1)+1)+1) as Comma5
FROM dbo.tblClient

 

[Felix18807]

--Comma Count
SELECT len(CAST([ADDRESSDETAIL] as varchar(1000))) - len(REPLACE(CAST([ADDRESSDETAIL] as varchar(1000)),CHAR(13),'')) as CommaCount, REPLACE(CAST([ADDRESSDETAIL] as varchar(1000)),CHAR(13),',') AS BREAKPOINTS, [ADDRESSDETAIL]
FROM TABLE

 

[Felix18807]

SELECT
CASE len(CAST([ADDRESSDETAIL] as varchar(1000))) - len(REPLACE(CAST([ADDRESSDETAIL] as varchar(1000)),CHAR(13),''))
WHEN 0 then '0'
WHEN 1 then '1'
WHEN 2 then '2'
WHEN 3 then '3'
WHEN 4 then '4'
WHEN 5 then '5'
END AS COUNTOBREAK
FROM dbo.tblClient

 

[drlex]

If your addresses don't have more than 4 lines and you have PARSENAME in your SQL server version (?2005 onwards)

Select parsename(replace(Addressdetail, char(13),'.'),4),
parsename(replace(Addressdetail, char(13),'.'),3),
parsename(replace(Addressdetail, char(13),'.'),2),
parsename(replace(Addressdetail, char(13),'.'),1)

soi là, soi carré

 

[Felix18807]

Unfortunately I have 5 lines to cater for... But thank you for your response!

 

[markros]

Without using a function you can use XML based split.


PluralSight Learning Library

 

[Qik3Coder]

I'd be careful using the parsename with addresses that are like to have a period in them. I was hoping you could nest the parsename, but found it interesting you get nulls for each field.

With that out of the way...

Offsite link:

http://www.sommarskog.se/arrays-in-sql.html

You've got questions and source code. We want both!