0

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
    ,Nome,Indirizzo,CAP,Comune,Prov
INTO table3
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--the record with the phone number after the [,]
SELECT DISTINCT SUBSTRING(Telefono, CHARINDEX(',', Telefono, 0) + 1, LEN(Telefono)) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,Fax
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--insert all the remaining rows
SELECT DISTINCT Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,fax
FROM #table1
WHERE Telefono NOT LIKE '%,%'
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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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