How to check if any of this !@#$%^&*()_-+ special characters exist in a string ?
i tried
SELECT PATINDEX('!@#$%^&*()_-+', 'test-');
SELECT PATINDEX('[!@#$%^&*()_-+]', 'test-');
SELECT PATINDEX('%[!@#$%^&*()_-+]%', 'test-');
but all returns 0, it should return 5, any help ?
The - is a special character in the LIKE or PATINDEX() pattern. If it is anywhere other than the first position, it is a range of characters -- such as all digits being represented by [0-9].
You can do what you want by moving the condition:
PATINDEX('%[-!@#$%^&*()_+]%', 'test-'),
Unfortunately, PATINDEX() patterns don't support an escape character. You can also express this logic as a LIKE and CASE:
(CASE WHEN 'test-' LIKE '%[-!@#$%^&*()_+]%' ESCAPE '$' THEN 1 ELSE 0 END)
Or using a "not" pattern:
(CASE WHEN 'test-' NOT LIKE '%[^0-9a-zA-Z]%' THEN 0 ELSE 1 END)
You can use negation:
SELECT PATINDEX('%[^a-Z]%', 'test-');
This will find a character NOT in the range a-Z.
SELECT PATINDEX('%[-+!_@()*^#$%&]%', 'test-');
this solves my issue returns 5 the positon of -.
Apperently order matters.
DECLARE @myString VARCHAR(100) ='test-'
IF (@myString LIKE '%[^a-zA-Z0-9]%')
PRINT 'Contains "special" characters'
ELSE
PRINT 'Does not contain "special" characters'
