SQL Server string parse?

Question

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,"m_welborn@yahoo.com , smeeken@gmail.com",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,"m_welborn@yahoo.com smeeken@gmail.com",10/28/2020 11:13'

"trying to remove" does not clearly define what your goal is. Are you trying to update the contents of this column? If so, what should the updated value be? If not, what should be the end-result of your "removal"? — SMor, Nov 05 '20 at 22:00
edited the question. but trying to Update #Line to remove that extra comma in between the two emails. — ChawleeJay, Nov 05 '20 at 22:04
I still have no idea what it is that you want. Can you please show your desired output, what you want to happen that isn't happening? And do you understand that SQL doesn't have Regular Expression support? And why would you want all that data in one column anyway? — pmbAustin, Nov 05 '20 at 22:06
Are there always spaces either side of the comma? perhaps you could get away with `REPLACE(line,' , ', '')` — codeulike, Nov 05 '20 at 22:11
@codeulike tbh im not sure the consistency of the spaces because im receiving the information from a vendor, parsing out the string data and separating each field to later be used as a dynamic insert into a table — ChawleeJay, Nov 05 '20 at 22:13
would you be better off reading the input as a CSV and separating it into columns? e.g. use Tasks -> Import Data or SSIS, it can usually handle CSV. A decent CSV parser should be able to handle those strings-that-contain-commas — codeulike, Nov 05 '20 at 22:15
e.g. see https://stackoverflow.com/questions/4123875/commas-within-csv-data — codeulike, Nov 05 '20 at 22:17

score 3 · Accepted Answer · answered Nov 05 '20 at 22:16

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,"m_welborn@yahoo.com , smeeken@gmail.com",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,"m_welborn@yahoo.com  smeeken@gmail.com",10/28/2020 11:13 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+

SQL Server string parse?

1 Answers1