How do I find specific symbol character combo in T-SQL specifically using LIKE-CodePudding

I have messy data that needs some cleaning, and, amongst other pattern matching, I am trying to remove any that is like: '-c[^ROAEH]'.

From another similar question, I tried:

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

Also:

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

And obviously the common:

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

and

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

Basically, I cannot manage to include the hyphen into the character search, as I get returned results without the string beginning with '-c' specifically.

I have done other pattern matching cleansing on this data, where '-' has been found, however not with the LIKE operator, so I wonder if there is a way to do it with the LIKE? For context, here's some of the other pattern matching code to clear out data that i've used:

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;

To put an example, some of the custnames I am trying to clear have names such as:

"Name Surname-cro"
"Random-Name-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?

CodePudding user response：

Seems that you're looking for the pattern '%-c[roaeh]%'

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

Such pattern also works with PATINDEX

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