mysql - 乘坐{

Question

SELECT DISTINCT 
    l.State, e.CorpID, e.LocID, tl.State AS 'Transferred From State',       
    e.TransferFromLocID AS 'Transferred From Location', 
    e.FirstName, e.LastName, e.SSN, 
    CONVERT(CHAR(10), e.CurrentBeginDate, 101) AS 'Start Date', 
    CONVERT(CHAR(10), e.BeginDate, 101) AS 'Begin Date', 
    CONVERT(CHAR(10), e.TermDate1, 101) AS 'Term Date', 
    (ISNULL(w.EligibleCode1, '') + ' ' + ISNULL(w.EligibleCode2, '') + ' ' + ISNULL(w.EligibleCode3, '') + ' ' + ISNULL(w.EligibleCode4, '') + ' ' + ISNULL(w.EligibleCode5, '')) AS 'Eligibility Code',   
    w.Disqualified, dcrs.StatusDescription AS 'DCR Status', 
    vt.TrackingCodes,      
    CONVERT(CHAR(10), w.DocRequestDate, 101) AS 'Doc Request Date',
    (CASE 
        WHEN w.RequestComplete = '0' 
          THEN  
            (CASE 
                WHEN w.EligibleCode1 IN ('I','A','G') 
                  THEN
                    (CASE 
                       WHEN (w.DocRecvd1 <> '11' AND w.DocRecvd2 <> '11') 
                         THEN '11'
                         ELSE '' 
                     END)
                WHEN w.EligibleCode1 IN ('B','J','Z') 
                  THEN
                    (CASE 
                        WHEN (w.DocRecvd1 NOT IN ('4','11') AND w.DocRecvd2 NOT IN('4','11')) 
                          THEN '4, 11' 
                        WHEN (w.DocRecvd1 <> '4' AND w.DocRecvd2 <> '4')   
                          THEN '4'
                        WHEN (w.DocRecvd1 <> '11' AND w.DocRecvd2 <> '11') 
                          THEN '11'
                        ELSE '' 
                     END)
                WHEN w.EligibleCode1 IN ('C') 
                  THEN
                    (CASE WHEN (w.DocRecvd1 NOT IN ('6','11') AND w.DocRecvd2 NOT IN('6','11')) THEN '6, 11' 
                    WHEN (w.DocRecvd1 <> '6' AND w.DocRecvd2 <> '6') THEN '6'
                    WHEN (w.DocRecvd1 <> '11' AND w.DocRecvd2 <> '11') THEN '11'
                    ELSE '' END)
                WHEN w.EligibleCode1 IN ('D','F') THEN
                    (CASE WHEN (w.DocRecvd1 NOT IN ('1','9') AND w.DocRecvd2 NOT IN('1','9')) THEN '1, 9' 
                    WHEN (w.DocRecvd1 <> '1' AND w.DocRecvd2 <> '1') THEN '1'
                    WHEN (w.DocRecvd1 <> '9' AND w.DocRecvd2 <> '9') THEN '9'
                    ELSE '' END)
                WHEN w.EligibleCode1 IN ('E') THEN
                    (CASE WHEN (w.DocRecvd1 NOT IN ('5','11') AND w.DocRecvd2 NOT IN('5','11')) THEN '5, 11' 
                    WHEN (w.DocRecvd1 <> '5' AND w.DocRecvd2 <> '5') THEN '5'
                    WHEN (w.DocRecvd1 <> '11' AND w.DocRecvd2 <> '11') THEN '11'
                    ELSE '' END)
                WHEN w.EligibleCode1 IN ('H') THEN
                    (CASE WHEN (w.DocRecvd1 NOT IN ('10','11') AND w.DocRecvd2 NOT IN('10','11')) THEN '10, 11' 
                    WHEN (w.DocRecvd1 <> '10' AND w.DocRecvd2 <> '10') THEN '10'
                    WHEN (w.DocRecvd1 <> '11' AND w.DocRecvd2 <> '11') THEN '11'
                    ELSE '' END)
                WHEN w.EligibleCode1 IN ('W','Y') THEN
                    (CASE WHEN (w.DocRecvd1 NOT IN ('4','11','13') AND w.DocRecvd2 NOT IN('4','11','13') AND w.DocRecvd3 NOT IN('4','11','13')) THEN '4, 11, 13' 
                    WHEN (w.DocRecvd1 NOT IN ('4','11') AND w.DocRecvd2 NOT IN('4','11') AND w.DocRecvd3 NOT IN('4','11')) THEN '4, 11'
                    WHEN (w.DocRecvd1 NOT IN ('4','13') AND w.DocRecvd2 NOT IN('4','13') AND w.DocRecvd3 NOT IN('4','13')) THEN '4, 13'
                    WHEN (w.DocRecvd1 NOT IN ('11','13') AND w.DocRecvd2 NOT IN('11','13') AND w.DocRecvd3 NOT IN('11','13')) THEN '11, 13'
                    WHEN (w.DocRecvd1 <> '4' AND w.DocRecvd2 <> '4' AND w.DocRecvd3 <> '4') THEN '4'
                    WHEN (w.DocRecvd1 <> '11' AND w.DocRecvd2 <> '11' AND w.DocRecvd3 <> '11') THEN '11'
                    WHEN (w.DocRecvd1 <> '13' AND w.DocRecvd2 <> '13' AND w.DocRecvd3 <> '13') THEN '13'
                    ELSE '' END)
            ELSE '' END)    
        ELSE '' END) AS 'Docs Needed',
        (CAST(w.DocRecvd1 AS VARCHAR(5)) + ',' + CAST(w.DocRecvd2 AS VARCHAR(5)) + ',' + CAST(w.DocRecvd3 AS VARCHAR(5))) AS 'Docs Received'
FROM 
    Employee e
LEFT JOIN 
    Location l ON (l.CorpID = e.CorpID AND l.LocID = e.LocID)
LEFT JOIN 
    Location tl ON (tl.CorpID = e.CorpID AND tl.LocID = e.TransferFromLocID)
INNER JOIN 
    EmpWOTC w ON w.Wormnumber = e.Wormnumber
LEFT JOIN 
    EmpTrackingCode tc ON tc.WormNumber = e.Wormnumber
INNER JOIN 
    TrackCd t ON t.TrackingCodeID = tc.TrackingCodeID
INNER JOIN 
    vw_TrackingCodes vt ON vt.WormNumber = e.WormNumber
LEFT JOIN 
    EmpWOTCDCRAddress dcra ON dcra.Wormnumber = e.Wormnumber
INNER JOIN 
    EmpWOTCDCRStatus dcrs ON dcrs.StatusID = dcra.StatusID
WHERE 
    w.CertRecvdDate IS NULL
    AND (e.BeginDate BETWEEN '1/1/2010' AND GETDATE())
    AND w.ICFIssueDate IS NOT NULL
    AND w.eligible = 1
    AND vt.TrackingCodes Like '%HDF%'
    AND vt.TrackingCodes NOT Like '%^%'
    AND vt.TrackingCodes NOT Like '%OSB%'
    AND vt.TrackingCodes NOT Like '%N%'
    AND vt.TrackingCodes NOT Like '%S%'
    AND w.Disqualified = 0
ORDER BY 
    l.State, e.SSN

我有这段代码，但 trackingcodes 列的输出带有 {xx}{yy} 等等如何在没有 { } 和它们之间的空格的情况下获得相同的答案？我将这些数据带入 Excel 以进行更多操作并在该数据上运行更多代码，但基本上我需要能够对我拥有的结果执行状态搜索。然后我想根据这些状态和那些 { 我无法让 Excel 找到它们来搜索不同的跟踪代码。当我删除它们然后将它们分开时，它工作得很好。

Answer 1

如果您需要做的只是删除括号，您可以使用 REPLACE():

REPLACE(REPLACE(REPLACE(vt.TrackingCodes,'}{',' '),'{',''),'}','') AS TrackingCodes

使用 3 个 REPLACE() 而不是 2 个，这样集合之间就只有空格了。