1

Ok, have tried [^a-zA-Z0-9], [^a-zA-Z0-9 ] (LIKE OR NOT LIKE), as well as PATINDEX('%[^a-zA-Z0-9]%',mycol) > 0

None of these exclude spaces and therefore always return valid entries- I want only those with non-numeric, non-alpha, and non-space. I know I have a record with HTML code in one varchar field. I also know I have special characters such as ?, #,*, ect.

Thanks for any assistance!

A G A
  • 11
  • 3
  • Well to ignore numbers you can use where ISNUMERIC(field) = 0 and CHARINDEX(' ' , field) = 0 -- for spaces and REPLACE(REPLACE(PARTNO,'/', ''),'-','') LIKE '%D12A%' -- Extend the non-alphanumer characers, give it a try might work –  Jul 03 '18 at 15:30
  • Possible duplicate of [^a-zA-Z0-9 excluding spaces?](https://stackoverflow.com/questions/9626283/a-za-z0-9-excluding-spaces) – Aaron Dietz Jul 03 '18 at 15:30
  • When I said spaces, I mean spaces between valid character (ex, a name), not JUST a space - I'm not clear on what the Replace (Replace(.. would do. These are NAME fields I'm executing it on, and I want to return ANY special characters that are non-alphanumeric, ignoring spaces. The examples provided are still returning valid entries. The referenced formula in the linked post (/[^a-zA-Z0-9\s]/gi) does not work on SQL Server 2012+. – A G A Jul 03 '18 at 15:42
  • I also tried LIKE '%[!@#$%&*()_-+={}[]/?|\~`.]%' with no success – A G A Jul 03 '18 at 16:02
  • Replace `'%[^a-zA-Z0-9]%` with `'%[^a-zA-Z0-9 ]%` (i.e. add a space to the chars you're matching) – JohnLBevan Jul 03 '18 at 16:09
  • @JohnLBevan - tried that too, it still returns VALID records. Ex, " SMITH " (leading and trailing (multiple) spaces is VALID - "SMITH #?" would NOT be a valid entry. In the case of the conditions provided, the VALID record is always returned. – A G A Jul 03 '18 at 17:12

0 Answers0