如何使用表中的相应值替换字符串中的ID？

I've got 2 tables in a SQL Server 2008 R2 database - Rules and Items

我在SQL Server 2008 R2数据库中有两个表 - 规则和项目

A Rules record has Id and Expression, eg:

规则记录具有Id和表达式,例如:

  1, "(1 AND 2)"

An Items record has Id and Name, eg:

Items记录具有Id和Name,例如:

  1, "Foo" 
  2, "Bar"

Is there a way to select all the rule expressions and substitute the items ids with their names in a single query?

有没有办法选择所有规则表达式,并在单个查询中用项目ID替换它们的名称?

SELECT Magic(Expression) FROM Rules WHERE Id = 1

will give me "(Foo AND Bar)"

会给我“(Foo AND Bar)”

I'm thinking of doing a .net console app, so I can leverage Regex, but if there's a way to do it in SQL, which isn't too messy, I'd rather go that route.

我正在考虑做一个.net控制台应用程序,所以我可以利用Regex,但如果有一种方法可以在SQL中做到这一点,这不是太乱,我宁愿走那条路。

2 个解决方案

#1

There's a thing called a CLR Stored Procedure. MSDN CLR Stored Procedure

有一种称为CLR存储过程的东西。 MSDN CLR存储过程

So you could use regex from .net. It's not fast though. You could do a simple rule like that with string functions in sql, but more complex ones would cause you serious pain. Think I'd be tempted to do it outside of sql myself, if there's a lot of it.

所以你可以使用.net的正则表达式。但它并不快。你可以在sql中使用字符串函数做一个简单的规则,但更复杂的规则会给你带来严重的痛苦。想想我自己很想在sql之外做这件事,如果它有很多的话。

#2

Your Items table should really have a Foriegn Key to the Rules table then it would be a simple matter of joining the 2 tables. However if an Item can be referenced from more than 1 rule they you'd need a XRef table that would allow you to have a many to many reference. Using a CLR to perform a REGEX expression is going to seriously slow down your Select statement. I would take a closer look at restructuring the tables to support what your application needs.

您的Items表应该具有Rules表的Foriegn Key,那么加入2个表就很简单了。但是,如果可以从多个规则引用项目,则需要一个允许您具有多对多引用的XRef表。使用CLR执行REGEX表达式会严重降低Select语句的速度。我会仔细研究重组表以支持您的应用程序需要什么。