Why in sql server does the below return false?
SELECT IIF('12345' like '\d+','True','False') as Integer
Asked
Active
Viewed 48 times
-2
-
5I guess **LIKE** does not accept regex. – Rahul May 22 '17 at 18:39
-
In MSSQL there is isnumeric https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql . – adopilot May 22 '17 at 18:43
-
@Rahul actually looks like `like` supports a variation of regex, https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql. – chris85 May 22 '17 at 18:43
-
1@chris85: That's not a variation of regex. That's simply a set of one or more characters. – Ken White May 22 '17 at 18:47
-
`isnumeric` is broken. https://stackoverflow.com/a/7400335/121544 – Shannon Severance May 22 '17 at 18:49
-
@KenWhite Well I guess I should have said you can allow a range of characters like in regex. – chris85 May 22 '17 at 18:54
2 Answers
0
REGEX is limited in SQL Server... you can use a few different methods. Here's one.
select case when patindex('%[^0-9]%','1234566') = 0 then 'TRUE' else 'FALSE' end
You can also look at ISNUMERIC() but read this function carefully before using... it returns false positives. E.g. SELECT ISNUMERIC($)
S3S
- 24,483
- 5
- 24
- 44
-
-
someone had said \d+ was equivalent to [0-9] . looks like they removed that post now – Steve May 22 '17 at 19:18
0
LIKE does not do regular expressions. It is a very simple pattern matching. SQL Server is a little more powerful then the standard LIKE.
'12345' not like '%[^0123456789]%' will match all strings that do not contain a non-numeric character. To make sure there is at least one digit and len('123456') > 0.
Edit: I forgot about character ranges which is more obviously correct, '12345' not like '%[^0-9]%'.
Shannon Severance
- 17,303
- 3
- 44
- 66