SQL选择与通配符URL匹配的行 [英] SQL to select rows that match a URL with wildcard

问题描述

我在数据库中有一个表，其中的一列包含类似 http://example.com/users/的URL */个人资料

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

为我提供了一个网址(例如 http://example.com/users/234/profile )，并希望选择db中与该网址匹配的所有行(在这种情况下，*是通配符).

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).

我正在考虑使用正则表达式来实现此目的，方法是将*替换为任何字符的正则表达式.但是不能确定选择哪种SQL或LINQ代码.

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.

你们有什么主意吗?

推荐答案

除非我遗漏了一些明显的内容，否则只需要一个简单的

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

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

在SQL查询中，正则表达式过于矫揉造作.

Regex is overkill and tricky in SQL queries.

注意:％字符等效于SQL表达式中的*通配符.

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

如果要使用LINQ并且仅针对SQL Server，则可以使用以下语法:

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;

这篇关于SQL选择与通配符URL匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！