Finding strings with invalid characters

Question

Post reply

Finding strings with invalid characters

doughora

Old Hand

Points: 321
More actions
January 29, 2024 at 11:48 am

#4369502

If there is a post with a solution, please direct me to it, otherwise I've not been able to find a solution to this item.
Within a Select, can the following list of valid characters be looked in a string and any containing a character outside this set be reported?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Try this, maybe?

https://www.sqlservercentral.com/scripts/patexclude8k

If len(string) > len(processed string)

you know that 'string' contained 'invalid' characters.

This reply was modified 10 months ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

Phil Parkin wrote:

Try this, maybe?
https://www.sqlservercentral.com/scripts/patexclude8k

That old function could use an updating imo. As this is the 2022 topic GENERATE_SERIES and STRING_AGG are available. Also, not to say it's incorrect but it seems suboptimal imo the outer SELECT FOR XML does not include an ORDER BY clause.

The PATINDEX function takes 2 parameters as arguments: 1) pattern, and 2) string, and returns the integer offset position of a pattern match. In order to search every character in the string the pattern uses the wildcard symbol '%' in the first and last positions, '%_%'. The hat symbol '^' is the logical negator, i.e. "not in ...". The full search pattern '%[^a-zA-Z0-9+-/]%' looks for any character NOT IN a-z, A-Z, 0-9, +, -, or /

For this question the OP seemingly only needs "... any [string] containing a character outside this set be reported" Minimally, the SIGN function could return 1/0 for true/false. Alternately, there's IIF or CASE WHEN

select sign(patindex('%[^a-zA-Z0-9+-/]%', v.string)) is_invalid
from (values ('Space Here'),
             ('NoSpace'),
             ('@#!$@#$%#$'),
             ('normal')) v(string);

/* equivalent w/IIF */
select iif(patindex('%[^a-zA-Z0-9+-/]%', v.string)>0, 1, 0) is_invalid
from (values ('Space Here'),
             ('NoSpace'),
             ('@#!$@#$%#$'),
             ('normal')) v(string);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können