c# - 奇怪的 String.Format() - 输入字符串的格式不正确

Question

我似乎不明白为什么我会遇到麻烦，因为我已经这样做了好几次，但是要格式化这个字符串 string fsql = string.Format(sql, fa); 我收到错误:输入字符串的格式不正确。

sql:

"DECLARE @BENTEST varchar(100)\r\nSET @BENTEST = '{1}'\r\n\r\nSELECT a.Period, a.NCR, a.Date_Created, a.Date_Done, a.Item, a.Qty, tc.TargetCost*a.Qty Cost, a.Aging FROM\r\n\t(SELECT \r\n\tnt.NCR, \r\n\tDATENAME(yy, nt.Date_Done) + '-' + RIGHT('0' + DATENAME(ISOWK, nt.Date_Done), 2) Period,\r\n\tnt1.Date_Created,\r\n\tnt.Date_Done,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%\"%', a.FirstParseStep)) ParsedItem FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"Item%', nt.ItemData) + LEN('\"Item\":\"'), 60) FirstParseStep) a) Item,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"ItemQty%', nt.ItemData) + LEN('\"ItemQty\":'), 60) FirstParseStep) a ) Qty,\r\n\tPCF.dbo.GetRealWorkDays(nt1.Date_Created, nt.Date_Done) Aging\r\n\tFROM PCF.dbo.NCRTask nt, PCF.dbo.NCRTask nt1\r\n\tWHERE nt1.NCR = nt.NCR\r\n\tAND (nt.TaskType = 5 OR nt.TaskType = 16)\r\n\tAND nt1.TaskType = 25\r\n\tUNION\r\n\tSELECT \r\n\tnt.NCR, \r\n\tDATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2) Period,\r\n\tnt.Date_Created,\r\n\tnull Date_Done,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%\"%', a.FirstParseStep)) ParsedItem FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"Item%', nt.ItemData) + LEN('\"Item\":\"'), 60) FirstParseStep) a) Item,\r\n\t(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM\r\n\t\t(SELECT SUBSTRING(nt.ItemData, PATINDEX('%\"ItemQty%', nt.ItemData) + LEN('\"ItemQty\":'), 60) FirstParseStep) a ) Qty,\r\n\tPCF.dbo.GetRealWorkDays(nt.Date_Created, GETDATE()) Aging\r\n\tFROM PCF.dbo.NCRTask nt\r\n\tWHERE nt.TaskType = 25\r\n\tAND nt.Date_Created < @BENTEST\r\n\tAND NOT EXISTS (SELECT 1 FROM PCF.dbo.NCRTask WHERE nt.NCR = NCR AND (TaskType = 5 OR TaskType = 16) AND Date_Done < @BENTEST)) a,\r\n\tavvik1.dbo.TargetCost tc\r\nWHERE a.Period = DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2)\r\nAND tc.ItemNo = a.Item"

sql 但已格式化以便您轻松阅读:

DECLARE @BENTEST varchar(100)
SET @BENTEST = '{1}'

SELECT a.Period, a.NCR, a.Date_Created, a.Date_Done, a.Item, a.Qty, tc.TargetCost*a.Qty Cost, a.Aging FROM
    (SELECT 
    nt.NCR, 
    DATENAME(yy, nt.Date_Done) + '-' + RIGHT('0' + DATENAME(ISOWK, nt.Date_Done), 2) Period,
    nt1.Date_Created,
    nt.Date_Done,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%"%', a.FirstParseStep)) ParsedItem FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"Item%', nt.ItemData) + LEN('"Item":"'), 60) FirstParseStep) a) Item,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"ItemQty%', nt.ItemData) + LEN('"ItemQty":'), 60) FirstParseStep) a ) Qty,
    PCF.dbo.GetRealWorkDays(nt1.Date_Created, nt.Date_Done) Aging
    FROM PCF.dbo.NCRTask nt, PCF.dbo.NCRTask nt1
    WHERE nt1.NCR = nt.NCR
    AND (nt.TaskType = 5 OR nt.TaskType = 16)
    AND nt1.TaskType = 25
    UNION
    SELECT 
    nt.NCR, 
    DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2) Period,
    nt.Date_Created,
    null Date_Done,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%"%', a.FirstParseStep)) ParsedItem FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"Item%', nt.ItemData) + LEN('"Item":"'), 60) FirstParseStep) a) Item,
    (SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM
        (SELECT SUBSTRING(nt.ItemData, PATINDEX('%"ItemQty%', nt.ItemData) + LEN('"ItemQty":'), 60) FirstParseStep) a ) Qty,
    PCF.dbo.GetRealWorkDays(nt.Date_Created, GETDATE()) Aging
    FROM PCF.dbo.NCRTask nt
    WHERE nt.TaskType = 25
    AND nt.Date_Created < @BENTEST
    AND NOT EXISTS (SELECT 1 FROM PCF.dbo.NCRTask WHERE nt.NCR = NCR AND (TaskType = 5 OR TaskType = 16) AND Date_Done < @BENTEST)) a,
    avvik1.dbo.TargetCost tc
WHERE a.Period = DATENAME(yy, @BENTEST) + '-' + RIGHT('0' + DATENAME(ISOWK, @BENTEST), 2)
AND tc.ItemNo = a.Item

fa 是一个字符串[]，它包括:

[0] = ""
[1] = date with format "YYYY-MM-DD"

我不明白为什么会出现错误。我已经为其他一些 sql:s 完成了它，但我没有遇到这个问题。有人知道怎么回事吗？

Answer 1

您的字符串中没有转义 }:

(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}%', a.FirstParseStep)) ParsedQuantity FROM
                                                  ^

你应该逃避它:

(SELECT SUBSTRING(a.FirstParseStep, 0, PATINDEX('%}}%', a.FirstParseStep)) ParsedQuantity FROM