表A有个字段为IP,VARCHAR类型,里面的数据都是IP地址,比如有
192.168.1.1
192.168.1.20
192.168.1.110
我想升序排序显示成
192.168.1.1
192.168.1.20
192.168.1.110
但是实际显示为
192.168.1.1
192.168.1.110
192.168.1.20它好像是根据最后位的第一位的数字的大小作为排序依据的,有啥办法可以实现吗?
192.168.1.1
192.168.1.20
192.168.1.110
我想升序排序显示成
192.168.1.1
192.168.1.20
192.168.1.110
但是实际显示为
192.168.1.1
192.168.1.110
192.168.1.20它好像是根据最后位的第一位的数字的大小作为排序依据的,有啥办法可以实现吗?
order by cast(SUBSTRING(ip,11,3) as int)
or
order by cast(replace(ip,'.','') as bigint)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-08-06 17:39:12
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(13))
insert [tb]
select '192.168.1.1' union all
select '192.168.1.20' union all
select '192.168.1.110' union all
select '192.168.1.22' union all
select '192.168.1.2'
--------------开始查询--------------------------
select
*
from
[tb]
order by
cast(parsename(col,4) as int),cast(parsename(col,3) as int),cast(parsename(col,2) as int),cast(parsename(col,1) as int)
----------------结果----------------------------
/*col
-------------
192.168.1.1
192.168.1.2
192.168.1.20
192.168.1.22
192.168.1.110(5 行受影响)*/