2

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 ?

xMilos
  • 1,519
  • 4
  • 21
  • 36
  • Those aren't special characters. Why do you want to check for their existence in SQL? Are you trying to "clean" user input or passwords? That would be the wrong way to do it. – Panagiotis Kanavos Jul 09 '18 at 09:15
  • 2
    In any case you *can* use eg `LIKE` or `PATINDEX` to check for multiple characters by [putting them inside square brackets](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/wildcard-character-s-to-match-transact-sql?view=sql-server-2017), escaped when necessary. Performance though will be **very bad**. The server wouldn't be able to use any indexes to check for characters in the middle of a string. What are you trying to do? There are probably better, faster ways to do it – Panagiotis Kanavos Jul 09 '18 at 09:16
  • @PanagiotisKanavos i use to check if input variable has any of them, and if anyone is found to return 0 – xMilos Jul 09 '18 at 09:20

5 Answers5

4

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

You can use negation:

SELECT PATINDEX('%[^a-Z]%', 'test-');

This will find a character NOT in the range a-Z.

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • This will also exclude numbers. Although, it's far safer than trying to exclude "special" characters – Panagiotis Kanavos Jul 09 '18 at 09:23
  • @HoneyBadger thank you for the answer however i need to handle only !@#$%^&*()_-+ because i replace other later – xMilos Jul 09 '18 at 09:24
  • @MasterYi so if I enter `αυτό εδώ` you'd allow it? If you want to use such matching for input cleaning it will fail far too easily – Panagiotis Kanavos Jul 09 '18 at 09:24
  • @PanagiotisKanavos yes i convert some of this characters, but if name have character - the conversion is getting messed up ( e.g αυτό is convertd to AJto instead of Auto ) – xMilos Jul 09 '18 at 09:26
  • @MasterYi why do such an arbitrary conversion then? There's absolutely nothing wrong with Unicode in SQL Server - in fact, it supported Unicode since day 1. SO itself uses SQL Server which is why I knew I could type a Greek string and have it appear in the page – Panagiotis Kanavos Jul 09 '18 at 09:27
  • @MasterYi in fact, I could type Greek, Arabic and Chinese all in the same comment and be certain it would appear correctly. No conversions are necessary. No special coding or encoding either - ASP.NET uses Unicode for *all* text*. As long as you write a correct SQL statement you can store and retrieve Unicode from/to nvarchar fields just fine – Panagiotis Kanavos Jul 09 '18 at 09:28
  • @PanagiotisKanavos client demands :) and I don't have choice to change – xMilos Jul 09 '18 at 09:29
  • @MasterYi client demands what? To mangle input and convert it to an unrecognizable form? That can't be used to satisfy *any* business process? I doubt it. If the customer said "I want to store all text safely" the answer would be "you already can". Just use `nchar` or `nvarchar` fields and parameterized queries – Panagiotis Kanavos Jul 09 '18 at 09:31
  • @MasterYi it's far more likely someone, somewhere tried to store Unicode in ASCII fields, or used string concatenation to pass values. That's a bug. You can't cover this up with cleaning or converting to arbitrary ASCII characters. Fixing the bug will be a lot easier – Panagiotis Kanavos Jul 09 '18 at 09:35
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/174639/discussion-between-master-yi-and-panagiotis-kanavos). – xMilos Jul 09 '18 at 09:40
0
SELECT PATINDEX('%[-+!_@()*^#$%&]%', 'test-');

this solves my issue returns 5 the positon of -.

Apperently order matters.

xMilos
  • 1,519
  • 4
  • 21
  • 36
  • 2
    The problem is, `-` appearing anywhere other than as the first character within square brackets makes it a character range specifier for the characters before and after it. I thought that was explicitly called out in the documentation, but other than it being used in an example (to point out that `[a-cdf]` matches a, b, c, d or f, but `[-acdf]` matches a, c, d, f or `-`), it's not highlighted that I can see. – Damien_The_Unbeliever Jul 09 '18 at 09:45
  • @Damien_The_Unbeliever there is no mention here https://learn.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-2017, thanks for explanation – xMilos Jul 09 '18 at 10:26
  • 1
    Yeah, like I said, not called out well. The example I was looking at was under [`LIKE`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017#using-wildcard-characters-as-literals) – Damien_The_Unbeliever Jul 09 '18 at 10:28
0
DECLARE @myString VARCHAR(100) ='test-'
IF (@myString LIKE '%[^a-zA-Z0-9]%')
    PRINT 'Contains "special" characters'
ELSE
    PRINT 'Does not contain "special" characters'
Killer Queen
  • 776
  • 9
  • 20
0
select patindex('%[' + char(45) + ']%', 'test-');

But symbol '-' not working in range values, replace this symbol on another symbol.

Next example find only writable symbols in @string:

declare @string varchar(max) = char(8) + char(9) + char(10) + char(11) + char(12) + char(13) + '-test string-';
select patindex(%[' + char(33) + '-' + char(255) + ']%', replace(@string, '-', '#'))
Peter Henry
  • 651
  • 5
  • 17
Kent
  • 1