1. sql查询问题关于IP段
IP地址被保存前应该转换为数字,仅在显示时转换为字符,处理时应该统一用数字处理.
create
function
dbo.fn_IP(@ip
varchar(15))
returns
bigint
begin
return
parsename(@ip,4)*cast(16777216
as
bigint)+parsename(@ip,3)*65536+parsename(@ip,2)*256+parsename(@ip,1)
end
declare
@tb
table
(id
int,ip
varchar(15))
insert
@tb
select
1,'10.210.128.207'
UNION
ALL
select
2,'10.210.128.206'
UNION
ALL
select
3,'10.210.128.205'
UNION
ALL
select
4,'10.210.128.204'
UNION
ALL
select
5,'10.210.128.203'
UNION
ALL
select
6,'10.210.128.202'
UNION
ALL
select
7,'10.210.128.201'
------------------------------调用---------------------------------------------------
select
id,ip,dbo.fn_IP(ip)
from
@tb
where
dbo.fn_IP(ip)
between
dbo.fn_IP('10.210.128.203')
and
dbo.fn_IP('10.210.128.205')
2. sql表的字段中，怎么查询有特殊标记符号的内容
create table tb(经度 varchar(20))
insert into tb values('93.3901')
insert into tb values('93.390392')
insert into tb values('93.3905')
insert into tb values('93.3906')
insert into tb values('93.39.7332')
insert into tb values('93.39.8904')
go
select case when parsename(经度,3) is null then parsename(经度,2) + '.' + parsename(经度,1)
else parsename(经度,3) + '.' + parsename(经度,2) + parsename(经度,1)
end 经度
from tb
drop table tb
/*
经度
----------------
93.3901
93.390392
93.3905
93.3906
93.397332
93.398904
（所影响的行数为 6 行）
*/
3. SQL 如何分列
declare @表1 table (id int,数据 varchar(10))
insert into @表1
select 1,'a b c' union all
select 2,'c a n' union all
select 3,'s c v'
select id,数据1=parsename(replace(数据,' ','.'),3),
数据2=parsename(replace(数据,' ','.'),2),
数据3=parsename(replace(数据,' ','.'),1) from @表1
/*
id 数据1 数据2 数据3
------------------
1 a b c
2 c a n
3 s c v
*/