SQL Server string parse?

DROP TABLE IF EXISTS #test

CREATE TABLE #test 
(
     line VARCHAR(500)
)

INSERT INTO #test (line)
VALUES ('1194125598,,191,3.95,194.95,Loan Payment,999999999,2779,"Melinda","Meeken",99999999,"[e-mail] , [e-mail]",10/28/2020 11:13')
        

SELECT 
    line,
    REPLACE(line,'%"%,%"%', '%"%|%"%')
FROM #test t

I have vendor info from a string and I’m trying to Update #line to remove the comma (,) in between the two emails. Does anyone have a suggestion?

Desired Output: '1194125598,,191,3.95,194.95,Loan Payment,999999999,2779,"Melinda","Meeken",99999999,"[e-mail] [e-mail]",10/28/2020 11:13'

Answer

Assuming it’s just the two emails and one comma, try the following:

DECLARE @Test table ( line varchar(500) );
INSERT INTO @Test VALUES
    ( '1194125598,,191,3.95,194.95,Loan Payment,999999999,2779,"Melinda","Meeken",99999999,"[e-mail] , [e-mail]",10/28/2020 11:13' );

SELECT
    STUFF (
        line,
        CHARINDEX ( ',', line, CHARINDEX ( '@', line ) ),
        1,
        ''
    ) AS new_line
FROM @Test;

Returns

+-----------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                   new_line                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| 1194125598,,191,3.95,194.95,Loan Payment,999999999,2779,"Melinda","Meeken",99999999,"[e-mail]  [e-mail]",10/28/2020 11:13 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+