如何在T-SQL中專門使用LIKE找到特定的符號 字符組合

我有需要清理的雜亂資料，并且在其他模式匹配中，我試圖洗掉任何類似：'-c [^ROAEH]'的資料。

從另一個類似的問題，我嘗試：

SELECT *
FROM #AcctKeysInt
WHERE NewCustName LIKE '[!-c][^ROAEH]'
ESCAPE '!'

還：

SELECT *
FROM #AcctKeysInt
WHERE NewCustName LIKE '[-c]%[^ROAEH]'

顯然是常見的：

SELECT *
FROM #AcctKeysInt
WHERE NewCustName LIKE '[-]c[^ROAEH]'

和

SELECT *
FROM #AcctKeysInt
WHERE NewCustName LIKE '%[-]c[^ROAEH]'

基本上，我無法將連字符包含在字符搜索中，因為我得到的回傳結果沒有專門以“-c”開頭的字串。

我已經對此資料進行了其他模式匹配清理，其中找到了“-”，但沒有使用LIKE操作員，所以我想知道是否有辦法使用LIKE? 對于背景關系，這里有一些其他模式匹配代碼來清除我使用過的資料：

DROP TABLE IF EXISTS #AccountKeysCleansed;
SELECT strAcctNbrCurrent
       ,strAcctNbrHistoric
       ,strCustName
       ,CustNameOld
       ,GarbageOld
       ,IntCustName
       ,IntGarbage
       ,CASE WHEN CHARINDEX('- c', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('- c', IntCustName) - 1))
             WHEN CHARINDEX('-cl', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('-cl', IntCustName) - 1))
             WHEN CHARINDEX('*CAS', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('*CAS', IntCustName) - 1))
             WHEN CHARINDEX('*CLOS', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('*CLOS', IntCustName) - 1))
             WHEN CHARINDEX('* CLOS', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('* CLOS', IntCustName) - 1))
             WHEN CHARINDEX('-  cl', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('-  cl', IntCustName) - 1))
             WHEN CHARINDEX('*pend CLOSE', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('*pend CLOSE', IntCustName) - 1))
             WHEN CHARINDEX('- To CLOSE', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('- To CLOSE', IntCustName) - 1))
             WHEN CHARINDEX('-To CLOSE', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('-To CLOSE', IntCustName) - 1))
             WHEN CHARINDEX('-=CLOSE', IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('-=CLOSE', IntCustName) - 1))
             WHEN CHARINDEX('(CLOSE',IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('(CLOSE', IntCustName) - 1))
             WHEN CHARINDEX('*deceas',IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('*deceas', IntCustName) - 1))
             WHEN CHARINDEX('-deceas',IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('-deceas', IntCustName) - 1))
             WHEN CHARINDEX('*legal',IntCustName) > 0 THEN TRIM(LEFT(IntCustName, CHARINDEX('*legal', IntCustName) - 1))
             WHEN IntCustName LIKE '*%' THEN RIGHT(IntCustName, LEN(IntCustName) - 1)
             ELSE IntCustName END NewCustName
       ,CASE WHEN CHARINDEX('- c', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('- c', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('-cl', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('-cl', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('*CAS', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('*CAS', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('*CLOS', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('*CLOS', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('* CLOS', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('* CLOS', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('-  cl', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('-  cl', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('*pend CLOSE', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('*pend CLOSE', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('- To CLOSE', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('- To CLOSE', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('-To CLOSE', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('-To CLOSE', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('-=CLOSE', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('-=CLOSE', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('(CLOSE', IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('(CLOSE', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('*deceas',IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('*deceas', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('-deceas',IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('-deceas', IntCustName)   1)   IntGarbage
             WHEN CHARINDEX('*legal',IntCustName) > 0 THEN RIGHT(IntCustName, LEN(IntCustName) - CHARINDEX('*legal', IntCustName)   1)   IntGarbage
             WHEN IntCustName LIKE '*%' THEN '*'   IntGarbage
             ELSE IntGarbage END newGarbage
INTO   #AccountKeysCleansed
FROM   #AccountKeysInt;

舉個例子，我試圖清除的一些客戶名稱具有以下名稱：

• “姓氏-cro”
• “隨機名稱-cah”
• "RandomName-caird"
• "Name-universal-Surname-coh" ... and so on. So I want SQL to find '-c[ROAEH]', to then add anything right of '-' to the pile of (column) Garbage.

Do you know how can I do this with the LIKE operator?

uj5u.com熱心網友回復：

似乎您正在尋找模式 '%-c[roaeh]%'

SELECT *
FROM #AcctKeysInt
WHERE NewCustName LIKE '%-c[roaeh]%'

這種模式也適用于PATINDEX

PATINDEX('%-c[roaeh]%', IntCustName) 

標籤：

上一篇：將列添加到表中并在之后自動UNION表

下一篇：不使用動態sql重寫select陳述句