[sql] MSSQL Regular expression

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

The answer is


As above the question was originally about MySQL

Use REGEXP, not LIKE:

SELECT * FROM `table` WHERE ([url] NOT REGEXP '^[-A-Za-z0-9/.]+$')

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.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to regex

Why my regexp for hyphenated words doesn't work? grep's at sign caught as whitespace Preg_match backtrack error regex match any single character (one character only) re.sub erroring with "Expected string or bytes-like object" Only numbers. Input number in React Visual Studio Code Search and Replace with Regular Expressions Strip / trim all strings of a dataframe return string with first match Regex How to capture multiple repeated groups?