Finding strings with invalid characters
-
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?
-
Try this, maybe?
https://www.sqlservercentral.com/scripts/patexclude8k
If len(string) > len(processed string)
you know that 'string' contained 'invalid' characters.
- Phil Parkin wrote:
Try this, maybe?
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply