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*
This question is related to
sql
sql-server
regex
Thank you all for your help.
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.
As above the question was originally about MySQL
Use REGEXP, not LIKE:
SELECT * FROM `table` WHERE ([url] NOT REGEXP '^[-A-Za-z0-9/.]+$')
Source: Stackoverflow.com