tognettm
asked on
Searching for Special Characters
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('%[!,@,#,$,%...]%
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
e.g., IF PATINDEX('%[!,@,#,$,%...]%
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
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
DECLARE @asc int
SET @asc = 0
WHILE @asc < 128
BEGIN
PRINT CHAR(@asc) + ', ' + str(@asc)
SET @asc = @asc + 1
END
hth
Paul
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('%[!@#$%%,...]%',
so the first % in the [ ] means that the second % is looked for as a percent sign...
hth
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('%[!@#$%%,...]%',
so the first % in the [ ] means that the second % is looked for as a percent sign...
hth
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"?
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"?
ASKER
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)+'-'
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.
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)+'-'
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.
That's a bear. AFAIK, on a case-insensitive installation, you would have to call a custom function to take case into consideration.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT PATINDEX ('%[' + char(32) + '-' + char(64) + ']%', 'ad;kajsdf;lkjadfioeuroieu
Paul