How to specify a "Like" on an integer column?_问答_开发者

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, 1开发者_StackOverflow社区238790, 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.

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.

It sounds like (pun intended) you may have a couple of issues with your design.

First, although the data element name "order number" may suggest a numeric, order numbers and the like are typically non-numeric (e.g. fixed-width text). For example, the International Standard Book Number (ISBN) is non-numeric, not least because the final character can be X. Even if all the allowable characters are digits, it doesn't necessarily follow that the values are numeric.

A good question to ask yourself is, does it make any sense to apply mathematical operations to these values? For example, does calculating the sum of a customer's order numbers give a meaning result? If the answer is no then the values are probably not numeric. Furthermore, given a requirement use an operator such as LIKE on the values is a strong indication that they are indeed non-numeric.

Second, there seems to be an implied relationship between the value ID = '123' and any ID that starts with the same characters. To return to the ISBN example, you can determine whether two different books were published by the same publisher (subject to knowing the publisher's codes) by splitting an ISBN into its composite groups. If your ID values have similar groupings, and you need to query against these grouping, you may find it easier to stores the 123 sub-element separate from the rest of the identifier and concatenate the parts for display only.

I don't know exactly how to do it in nHibernate but since intergers are of a limited size you could just append the restriction the check for the possible ranges

The SQL Versions is like this

With cte as (
select top 10000000 row_number() over(order by t1.number) as N
from   master..spt_values t1 
       cross join master..spt_values t2)
SELECT * FROM cte
WHERE
n = 123
or n between  1230 and 1239
or n between  12300 and 12399
or n between  123000 and 123999
or n between  1230000 and 1239999

Since this is possible in Linq (with NHibernate) it's most likely also possible with Criteria. You just need to convert it to an string/varchar before calling the like. Maybe there is a "convert to string" projection?

In Linq this would simply be:

.Where(x => x.Id.ToString().StartsWith("123"))

in your mapping for the class you can define a string column that uses a formula to populate it. the conversion from int to string is done using the formula and you'd query against that without having to change your database.