SQL to select rows that match a URL with wildcard

Question

I have a table in the db with one column containing a URL like http://example.com/users/*/profile

I am given a URL (like http://example.com/users/234/profile) and want to select all the rows in the db that match the url (in this case * is a wildcard).

I was thinking of using Regex to do this, by replacing the * with regex for any character. But am unsure as to what the SQL or LINQ code should be for the selection.

Do you guys have any ideas?

score 7 · Accepted Answer · edited Mar 20 '14 at 05:51

7

Unless I am missing something obvious, this should just require a simple LIKE query.

SELECT *
FROM YourTable
WHERE URL LIKE 'http://example.com/users/%/'

Regex is overkill and tricky in SQL queries.

Note: The % character is the equivalent of the * wildcard in SQL expressions.

If you want to use LINQ and are only working against SQL Server, you can use this syntax:

var results =
        from yt in YourTable
        where SqlMethods.Like(yt.URL, "http://example.com/users/%/")
        select yt;

edited Mar 20 '14 at 05:51

jdphenix

15,022
3
41
74

answered Aug 04 '10 at 22:37

JohnFx

34,542
18
104
162

Is there an easy way to do that in linq? Seems they only have startswith, contains, etc... – rksprst Aug 05 '10 at 02:36

score 2 · Answer 2 · 2010-08-04T22:52:59.173

2

It's as simple as doing a like, but replacing the * character by the % character since it's the wildcard character used by T-SQL (I am assuming you are on SQL Server since this is C# and Linq related)

SELECT * 
FROM YourTable
WHERE @TheUrlIWantToMatch LIKE REPLACE(URL, '*', '%')

edited Aug 04 '10 at 22:52

answered Aug 04 '10 at 22:39

1

Like doesn't work that way. You can't have a wildcard in the expression before the LIKE keyword. – JohnFx Aug 04 '10 at 22:47
1

Oops I put them in the wrong order. I edited it thanks for pointing that out – Aug 04 '10 at 22:53

SQL to select rows that match a URL with wildcard

2 Answers2

Linked