我必须采用什么数据类型'01 .10.10'在Sql中 [英] What Datatype I Have To Take For '01.10.10' In Sql

问题描述

大家好，



我有点混淆。



在Sql Server 2008 R2中。



i正在为参考创建一个表格，在那里我被困住了，



什么数据类型应该是'01 .10.01'之类的数字，就像参考＃。



插入后，我必须在范围之间选择这些引用。



所以，任何人都可以帮助我...



谢谢

Hi guys,

i'm little bit confuse.

In Sql Server 2008 R2.

i was creating a table for references, where i got stuck n confused for,

what data type should i take for numbers like '01.10.01' its like Reference#.

after inserting, i have to select these references between range.

So, can anyone please help me...

Thanks

推荐答案

我会将您的引用号存储为数据库中的整数。因此，当然需要进行转换，但这很简单。这假设标识符的每个部分都适合一个字节，即0到255之间。然后在你的代码中你需要这样做：

I would store your reference numbers as integers in the database. For that of course a transformation is required, rather simple though. This assumes that each part of the identifier fits into a byte i.e. is between 0 and 255. Then in your code you need to do this:

// convert to integer
int dbValue = ((int)xx << 16) + ((int)yy << 8) + zz;

// convert back to components
byte xx = (byte)(dbValue >> 16);
byte yy = (byte)(dbValue >> 8);
byte zz = (byte)dbValue;

如果你的标识符由一个类包装，这不应该是复杂的。在数据库中，您只是比较整数，您可以直接在...和... 之间使用。

if your identifier is wrapped by a class this shouldn't be tto complicated to achieve. In database you are just comparing integers and you can use between ... and ... directly.

Varchar应该可以工作，但是它可能取决于整理是什么，但是数字格式化你拥有它们的方式应该没问题。

可以使用Between，Greater Than / Less Than和Like进行比较，参见下面的示例：

Varchar should work, but it may depend on what the collation is, but with numbers formatted the way you have them it should be okay.
Comparisons can be done using the Between, Greater Than/Less Than and Like, see example below:

declare @r table (ref varchar(50));
insert into @r
select '01.10.01' ref union all
select '01.28.80' ref union all
select '02.20.02' ref union all
select '02.20.03' ref union all
select '02.20.50' ref union all
select '02.25.90' ref union all
select '02.30.80' ref union all
select '12.02.01' ref union all
select '12.28.80';

select * from @r where ref between '02.20.02' and '12.02.01';
--result
/*
02.20.02
02.20.03
02.20.50
02.25.90
02.30.80
12.02.01
*/
select * from @r where ref >= '12.02.01';
--result
/*
12.02.01
12.28.80
*/
select * from @r where ref like '02.20%';
--result
/*
02.20.02
02.20.03
02.20.50
*/

我不确定它的效率如何。

我曾经有一个名为SortCode的字段，类似于你所拥有的字段而不是2位数字，它们在哪里3位数。

也只使用Like来查询它们，从未想过使用Between和Greater Than / Less Than。

I am not sure how efficient it is though.
I used to have fields called SortCode similar to what you have but instead of 2 digit numbers, they where 3 digit.
Also only ever used the Like to query them, never thought of using Between and Greater Than/Less Than.

这篇关于我必须采用什么数据类型'01 .10.10'在Sql中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！