Menu
I have the following REGEX: ^[-A-Za-z0-9/.]+$
This currently checks whether the value entered into a textbox matches this. If not, it throws an error.
I need to check whether anything has already gone into the database that doesnt match this.
I have tired:
SELECT * FROM *table* WHERE ([url] NOT LIKE '^[-A-Za-z0-9/.]+$') SELECT * FROM *table* WHERE PATINDEX ('^[-A-Za-z0-9/.]+$', [url]) UPDATE
So after a bit of research I've realised I don't think I can use REGEXP.
I thought I could do something like this? Its not giving me the expected results but its running unlike anything else. Can anyone spot anything wrong with it?
SELECT *, CASE WHEN [url] LIKE '^[-A-Za-z0-9/.]+$' THEN 'Match' ELSE 'No Match' END Validates FROM *table* 
879
We use regular expressions to define specific patterns in T-SQL in a LIKE operator and filter results based on specific conditions. We also call these regular expressions as T-SQL RegEx functions. In this article, we will use the term T-SQL RegEx functions for regular expressions.
The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space) – but then you'd need to use brackets every time you referred to that column. The newer tools add them everywhere just in case or for consistency.
The [] construct in a regex is essentially shorthand for an | on all of the contents. For example [abc] matches a, b or c. Additionally the - character has special meaning inside of a [] . It provides a range construct. The regex [a-z] will match any letter a through z.
This is what I have used in the end:
SELECT *, CASE WHEN [url] NOT LIKE '%[^-A-Za-z0-9/.+$]%' THEN 'Valid' ELSE 'No valid' END [Validate] FROM *table* ORDER BY [Validate] Disclaimer: The original question was about MySQL. The SQL Server answer is below.
In MySQL, the regex syntax is the following:
SELECT * FROM YourTable WHERE (`url` NOT REGEXP '^[-A-Za-z0-9/.]+$') Use the REGEXP clause instead of LIKE. The latter is for pattern matching using % and _ wildcards.
Since you made a typo, and you're using SQL Server (not MySQL), you'll have to create a user-defined CLR function to expose regex functionality.
Take a look at this article for more details.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
