i am looking for some help on the following,
I have a cell of information like this:
7 Richview Office Park, Clonskeagh, Dublin 14, Dublin
Now this will go into one cell of data for example in Excel.
I want to seperate this out into wat ever amount of cells is needed.
So with this example i will need 4 cells to hold each piece of info (the comma is the seperater.)
In Excel if i were copying this data i could use something like the text to colum function. But i don't know how to do this using T-SQL
so the infomration in my table will be two colimns
One with the Customer number
One with the Address.
The address can have different amount of info in it.
for example some address might just be No 11, Dublin
and others will be longer.
I would like the address cell to be split out per comma.
So for No11, Dublin this would be two cells.
Can anyone give me some advice on how to do this?
Drop Table CUSTOMER_ADDRESS_DUMP
CREATE TABLE CUSTOMER_ADDRESS_DUMP
(id int null,
INSERT INTO CUSTOMER_ADDRESS_DUMP(id,all_address)
VALUES (10001055,'Drumhaughley, Killoe, Co. Longford')
INSERT INTO CUSTOMER_ADDRESS_DUMP (id,all_address) VALUES (100741,'Unit T28 Stillorgan Industrial, Rowan Avenue, Stillorgan, Blackrock, Dublin, Dublin')
--This will give you the first word before a comma
SET Address_1 = LEFT(all_address,CHARINDEX(',',all_address)-1)
-- This will give you the second one.
SET Address_2 = LEFT(
--Trying to get the remaining but not sure how to , need help please:
-- This iwll give you the third Entry
SET Address_3 = RIGHT(all_address,LEN(all_address)-CHARINDEX(',',all_address)-CHARINDEX(',',all_address))