如何指定“赞"在整数列上? [英] How to specify a "Like" on an integer column?

问题描述

这个让我感到难过，并认为我会向SO社区提出帮助.

用户想要选择所有以特定ID开头的订单，例如:

123 将返回 123 ， 12345 ， 1238790 ，等等.但是ID是int列. /p>

我正在使用nHibernate，当前行是:

criteria.Add(Restrictions.Eq("Id", itemId));

但是那只会让我返回123.我可以执行Restrictions.Like，但是它将转换为SQL LIKE子句，并且在int col上不起作用.

有什么想法吗?

对不起，数据库是SQL Server 2008

解决方案

不幸的是，您没有指定要使用的哪个数据库(SQL只是查询语言....)，但是，如果您使用的是SQL Server(Microsoft RDBMS产品)，则可以创建类型为VARCHAR(15)的计算列来保存您的INT的字符串表示形式，然后在该列上进行搜索....

ALTER TABLE dbo.YourTable
   ADD IdAsString AS CAST(Id AS VARCHAR(15)) PERSISTED    -- PERSISTED might not work - depending on your version of SQL Server

SELECT (list of columns)
FROM dbo.YourTable
WHERE IdAsString LIKE '123%'

这是否真的具有商业意义，是一个完全不同的故事……(我同意奥德和马特·鲍尔...)

但是由于这是现在的字符串列，因此您应该能够在NHibernate中使用您提到的Restrictions.Like方法.

This one has me stumped and thought I would pose it to the SO community for help.

A user wants to select all orders that start with a certain ID, example:

123 would return 123, 12345, 1238790, etc. ID is an int column however.

I'm using nHibernate and my line currently is:

criteria.Add(Restrictions.Eq("Id", itemId));

but that's only going to return me 123. I can do a Restrictions.Like, but that converts to a SQL LIKE clause and that won't work on an int col.

Any ideas?

EDIT: Sorry, the DB is SQL Server 2008

解决方案

Unfortunately, you didn't specify what database you're using (SQL is just the query language....), but if you're on SQL Server (the Microsoft RDBMS product), then you could create a computed column of type VARCHAR(15) to hold a string representation of your INT, and then just search on that....

ALTER TABLE dbo.YourTable
   ADD IdAsString AS CAST(Id AS VARCHAR(15)) PERSISTED    -- PERSISTED might not work - depending on your version of SQL Server

SELECT (list of columns)
FROM dbo.YourTable
WHERE IdAsString LIKE '123%'

Whether that really makes business sense, is a totally different story..... (I agree with Oded and Matt Ball...)

But since that's a string column now, you should be able to use your Restrictions.Like approach in NHibernate as you mention.

这篇关于如何指定“赞"在整数列上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！