I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the same). To do these I had to make some selects but how can I do the stored procedure to do that in a easier way??? When there are 2 telephone number separated with ',' with the first SELECT I insert to table3 the row with the phone number before the ',':

SELECT DISTINCT SUBSTRING(Telefono, 1, CHARINDEX(',', Telefono, 0) - 1) AS Telefono
INTO table3
FROM #table1
WHERE Telefono LIKE '%,%'

--the record with the phone number after the [,]
SELECT DISTINCT SUBSTRING(Telefono, CHARINDEX(',', Telefono, 0) + 1, LEN(Telefono)) AS Telefono
FROM #table1
WHERE Telefono LIKE '%,%'

--insert all the remaining rows
FROM #table1
WHERE Telefono NOT LIKE '%,%'

1 Answer 1


Before you start thinking about a solution, try to evaluate if your requirements make sense.

Is it really the best solution to create duplicate rows if multiple phone numbers exist or would it be better to create a new table PhoneNumbers with a foreign key?

And splitting data can be done more elegantly in the tool that loads the data into the database. If you have to do it in SQL, the solution depends on the database system you use. For instance, here is a solution for SQL Server: https://stackoverflow.com/q/5493510/1595345

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.