SQL Server字符串解析

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,"[e-mail] , [e-mail]",10/28/2020 11:13')

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

我有一个字符串中的供应商信息，我正在尝试更新#line以删除两封电子邮件之间的逗号(，(。有人有什么建议吗？

所需输出：'1194125598,,191,3.95,194.95,Loan Payment,999999999,2779,"Melinda","Meeken",99999999,"[e-mail] [e-mail]",10/28/2020 11:13'

Answer 1

假设只有两封电子邮件和一个逗号，请尝试以下操作：

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;

返回

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