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*

Are you using MSSQL or MySQL? They are two, different, competing RDBMS products from different suppliers and which have different syntax. — eggyal, Mar 23 '15 at 10:57

score 29 · Accepted Answer · edited Nov 02 '21 at 20:15

29

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]

edited Nov 02 '21 at 20:15

Braiam

1
11
47
78

answered Mar 23 '15 at 10:30

Clare Barrington

1,135
1
11
28

10

I am very sure the answer does not work as intended, because MS-SQL supports only a very limited subset of RegExp like operators: https://msdn.microsoft.com/en-us/library/ms187489(v=sql.105).aspx, hence the expression provided in [] matches one single character from the set ^-A-Za-z0-9/.+$ and the rest is consumed by % which will match any string in LIKE expression. – Mr. Napik May 17 '16 at 12:09
1

@Mr.Napik - The idea is sound though it is collation dependent whether or not it actually works as written. `^` acts as a negation. So instead of saying "check every character matches the pattern" it checks that there are no characters that don't match the pattern. It probably needs a binary collate clause to actually work as intended though. e.g. see `SELECT char(number) FROM master..spt_values WHERE type='P' and number between 1 and 255 and CHAR(number) NOT LIKE '[^-A-Za-z0-9/.+$]' COLLATE Latin1_General_Bin` with and without it. – Martin Smith Oct 23 '16 at 19:04

Lucas Trzesniewski · Answer 2 · 2019-02-13T09:14:28.520

19

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.

edited Feb 13 '19 at 09:14

answered Mar 23 '15 at 09:06

Lucas Trzesniewski

50,214
11
107
158

I am receiving 'an expression of non-boolean type specified in a context where a condition is expected' do I need to reference anything else? – Clare Barrington Mar 23 '15 at 09:15
Hmm, I think it may be related to Phylogenesis' comment... Don't use `[...]` to escape column names, that's T-SQL syntax. Either drop them if possbile or use backticks (`) – Lucas Trzesniewski Mar 23 '15 at 09:30
I've altered it, to the above code. I am now receiving: Incorrect syntax near 'REGEX'. expecting between in or like. I've googled this and it seems that its a type cast error? – Clare Barrington Mar 23 '15 at 10:01
I'm starting to wonder whether I can in actual fact use REGEXP in SQL Management Studio 2012 – Clare Barrington Mar 23 '15 at 10:12
Are you sure you are using MySQL and not MS SQL? SQL Server Management Studio is generally used with the latter. – George Cummins Mar 23 '15 at 10:18
1

@Clare AHA! Yeah, that's a totally different thing! In MSSQL you need to use a [CLR function](https://msdn.microsoft.com/en-us/library/w2kae45k.aspx) to use regexes. – Lucas Trzesniewski Mar 23 '15 at 10:51
@LucasTrzesniewski, the link you have provided in answer is not valid at the moment. Can you provide the valid link – Jitendra Pawar Feb 13 '19 at 07:11
@JitendraPawar I changed the link to use the wayback machine – Lucas Trzesniewski Feb 13 '19 at 09:14

Matt · Answer 3 · 2020-07-14T08:39:58.540

-3

As above the question was originally about MySQL

Use REGEXP, not LIKE:

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

edited Jul 14 '20 at 08:39

answered Mar 23 '15 at 09:06

Matt

14,906
27
99
149

1

@Azimuth Regular expression information on SQL Server can be found here https://learn.microsoft.com/en-us/sql/relational-databases/scripting/search-text-with-regular-expressions – Matt Feb 05 '18 at 14:12
1

@Matt The link you provided refers to __editing__ the query, rather than valid syntax of SQL Server. More important thing is that even in SQL Server 2017 running similar query returns `An expression of non-boolean type specified in a context where a condition is expected, near 'REGEXP'.` error - I assume there is no such keyword yet. – bjauy Feb 08 '18 at 12:04
Yes, as @bjauy pointed, there is no such keyword as `REGEXP`. So please update your answer with correct SQL code. – Azimuth Feb 15 '18 at 08:24

MSSQL Regular expression

3 Answers3

MySQL

SQL Server

Linked