Solved

Searching for Special Characters

Posted on 2003-11-11
8
1,476 Views
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.

Thanks,

MT
0
Comment
Question by:tognettm
8 Comments
 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9724857
Try something like this:

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

Paul
0
 
LVL 9

Expert Comment

by:PaulBarbin
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
BEGIN
      PRINT CHAR(@asc) + ', ' + str(@asc)
      SET @asc = @asc + 1
END

hth
Paul
0
 
LVL 50

Expert Comment

by:Lowfatspread
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...

hth



0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 69

Expert Comment

by:ScottPletcher
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"?
0
 

Author Comment

by:tognettm
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
   Begin
   ...
   End,

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.  

0
 
LVL 69

Expert Comment

by:ScottPletcher
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.
0
 
LVL 9

Accepted Solution

by:
PaulBarbin earned 500 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)

SET @name = 'micHael1' COLLATE LATIN1_GENERAL_CS_AS
SET @pattern = '%[A-H]%' COLLATE LATIN1_GENERAL_CS_AS

SELECT patindex (@pattern,@name)

Paul
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now