Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Searching for Special Characters

Posted on 2003-11-11
Medium Priority
Last Modified: 2012-05-04
I am trying to write a validation routine where I would use PATINDEX/CHARINDEX to locate either valid or invalid characters in a varchar variable.  I am already checking for the presence of Upper Case characters, lower case characters, and numbers, but I am trying to locate special characters (by range, or group) without have to write a check for each special character individually.  

e.g., IF PATINDEX('%[!,@,#,$,%...]%', @Variable)>0

Is this even possible?  I thought that I had read somewhere that it was but just can't seem to locate it now.


Question by:tognettm
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 9724857
Try something like this:

SELECT PATINDEX ('%[' + char(32) + '-' + char(64) + ']%', 'ad;kajsdf;lkjadfioeuroieur&adsfadf')


Expert Comment

ID: 9724872
The code above gives you the ability to select a range of values.  Run the code below to check out the values that you need to use.

DECLARE @asc int
SET @asc = 0

WHILE @asc < 128
      PRINT CHAR(@asc) + ', ' + str(@asc)
      SET @asc = @asc + 1

LVL 50

Expert Comment

ID: 9725205
you basically had it

PATINDEX('%[!@#$,...]%', @Variable)>0

you don't need comma to separate the values....

if you want to search for % or _  or [ or ] then you need to define an escape character (or double up the [] eg [[])

PATINDEX('%[!@#$%%,...]%', @Variable) ESCAPE '%' >0

so the first % in the [ ] means that the second % is looked for as a percent sign...


How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

LVL 69

Expert Comment

by:Scott Pletcher
ID: 9725397
Paul's approach looks generally good to me if you want to do checks for range(s) of characters.

But what do you mean by "valid", "invalid" and "special characters"?  Other than letters, upper and lower case, and numbers, what characters do you want to consider "valid"?

Author Comment

ID: 9728044
Paul's answer meets my needs for the orginal portion of the question and I will be awarding him the original 250 points.  Thanks.  

I would like to add more to this though, along the same lines and am increasing the point value as a result.

In looking for the valid Upper Case range of letters...

 IF patindex ('%[A-H]%','micHael1')<1

The return results of PATINDEX is 3...this represents the letter 'c' (lower case) in the above string.  The same is true when I change the range values as follows:

IF PATINDEX('%['+CHAR(67)+'-'+CHAR(90)+']%','micHael1')
What gives?  What I am looking for here is to search the string for the presence of at least one Upper Case letter, and I will also be doing the same for a lower case letter.  Both need to be present.  

 Should I ask this as a separate question or is increasing the points then splitting them amongst the experts here enough since this addition is related to the first problem.  

LVL 69

Expert Comment

by:Scott Pletcher
ID: 9731560
That's a bear.  AFAIK, on a case-insensitive installation, you would have to call a custom function to take case into consideration.

Accepted Solution

PaulBarbin earned 1500 total points
ID: 9731817
Scott, I thought it was a collation problem too, but I can't seem to get it to work even when I explicitly set the collation for the literal (SQL 2K).

Am I doing something wrong here?

DECLARE @name varchar(30), @pattern varchar(30)


SELECT patindex (@pattern,@name)


Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question