MSSQL Regular expression

Question

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* 

Answer 1

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] 

Answer 2

Disclaimer: The original question was about MySQL. The SQL Server answer is below.

MySQL

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.

---

SQL Server

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.