How to separate comma-split data from 1 field and move into 2 separate fields in SSMS?

Question

I DO NOT need to create the 2 new fields to move the data to. The fields have already been created. I just need to move the comma-separated data from 1 field into the 2 fields that have already been created

I have 1 column with city and state data. I need to move the data from that 1 column into the 2 columns- one for city and for state.

I need to take the comma-split data from Address1 and place into Address2 and Address3 column.

Address1 (column)
Anywhere, USA

Address2 (column)  Address3 (column)
Anywhere            USA

Sample data and desired results would be helpful. – Mark Kram May 17 '19 at 21:45 — Mark Kram, May 17 '19 at 21:45

Piotr Palka · Answer 1 · 2019-05-20T15:15:19.837

0

You may use solution from How to split a comma-separated value to columns to create an UPDATE statement:

CREATE TABLE #test (Address1 varchar(100),Address2 varchar(100),Address3 varchar(100))

INSERT INTO #test (Address1) VALUES ('Anywhere, USA')
INSERT INTO #test (Address1) VALUES ('Anywhere')

UPDATE #test set
Address2 = Substring(Address1 , 1,Charindex(',', Address1)-1),
Address3 = Substring(Address1 , Charindex(',', Address1 )+1, LEN(Address1))
WHERE Address1 LIKE '%,%'

SELECT * FROM #test
DROP TABLE #test

edited May 20 '19 at 15:15

answered May 17 '19 at 22:54

Piotr Palka

3,086
1
9
17

I am getting the follwing error message:Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated. – TechieGirlinCali May 17 '19 at 23:35
Probably some of your data do not have any commas, I updated the code. – Piotr Palka May 20 '19 at 15:15
Thank you, Piotr. I was able to split the data thanks to the coding you provided. – TechieGirlinCali May 20 '19 at 21:35

How to separate comma-split data from 1 field and move into 2 separate fields in SSMS?

1 Answers1