Splitting a field by delimiter char(10)

[iritchie]

*(Apologies, I posted this in the SQL Server forum first)


Hello all,


I am trying to write a query which breaks down a single address field into individual fields, with char(10) or a carriage return as the delimiter.
"empltable" is the table I am using, and "address" is the field I am looking to split.

So far I have managed to:

Split the field into 3

SELECT emplid, name,
CHARINDEX(char(10),address) first_cr,
 
CHARINDEX(char(10), address, CHARINDEX(char(10), address)+1) second_cr,
 
LEFT(address, charindex(char(10), address, -1)) as [address1],
 
SUBSTRING(address, CHARINDEX(char(10), address)+1, CHARINDEX(char(10), address, CHARINDEX(char(10), address)+1)-CHARINDEX(char(10), address)) as [address2],
 
RIGHT(address,LEN(address)-CHARINDEX(char(10),address,CHARINDEX(char(10),addr ess)+1 )) as [address3]
 
FROM empltable
 

...but this only works on the test database, where I am inserting carriage returns into the field to allow the query to work. Basically, when there are less than 2 carriage returns in the address field, the query fails, as there is no error handling in the query.
So, on from that, I tried to introduce NULLIF into the query:

SELECT SUBSTRING(address, 1, NULLIF(CHARINDEX(char(10), address) - 1, -1)) AS [Address1],
SUBSTRING(address, CHARINDEX(char(10), address) + 1, LEN(address)) AS [Address2]
FROM empltable
 

...This splits the address field into 2. I am having difficulty returning the middle section though without encountering the errors.
Ideally I would rewrite it to handle any number of returns!

Can someone please help!?

[pbmods]

Heya, Iritchie. Welcome to TSDN!

Please use CODE tags when posting source code:

[CODE=MySQL]
MySQL code goes here.
[/CODE]

[iritchie]

Heya, Iritchie. Welcome to TSDN!

Please use CODE tags when posting source code:

MySQL code goes here.
 

Thanks! :)

Will use the code tags from now...

I also used the left and right reverse to get the first and last entries in the string, but again, struggle to come up with the results for the part in the middle:

SELECT emplid, name,
    LEFT(address, NULLIF(CHARINDEX(char(10), address) - 1, -1)) AS [Address1],
           RIGHT(address, ISNULL(NULLIF(CHARINDEX(char(10), REVERSE(address)) - 1, -1),
             LEN(address))) AS [Address2]
FROM empltable