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
*/