检查表中的数据是否存在问题,另外,可以改造一下函数: ----------------------------------------------------------------------------- CREATE FUNCTION f_IP2Int(@ip char(15)) RETURNS bigint AS BEGIN DECLARE @re bigint SET @re= PARSENAME(@ip,4)*CAST(16777216 as bigint) +PARSENAME(@ip,3)*65536 +PARSENAME(@ip,2)*256 +PARSENAME(@ip,1) RETURN @re END GO
create table table1(ip nvarchar(15)) insert into table1 select N'137.82.0.0' insert into table1 select N'127.0.0.1' insert into table1 select N'127.0.0.1' insert into table1 select N'127.0.0.1' insert into table1 select N'137.82.0.1' insert into table1 select N'127.0.0.1' insert into table1 select N'84.204.124.0' insert into table1 select N'127.0.0.1' GO CREATE FUNCTION f_IP2Int(@ip char(15)) RETURNS bigint AS BEGIN DECLARE @re bigint SET @re= PARSENAME(@ip,4)*CAST(16777216 as bigint) +PARSENAME(@ip,3)*65536 +PARSENAME(@ip,2)*256 +PARSENAME(@ip,1) RETURN @re END GO select dbo.f_IP2Int(IP) from table1drop table table1 drop function f_IP2Int go
libin_ftsafe(子陌红尘|潇湘剑公子@dev-club) 大师: 数据没有问题啊 执行select * table1 where field2 is null 符合条件的记录如下field1字段值: 137.82.0.0 127.0.0.1 127.0.0.1 127.0.0.1 137.82.0.1 127.0.0.1 84.204.124.0 127.0.0.1 按照你的函数,执行下面语句: update table1 set field2=dbo.f_ip(field1) where field2 is null 报错: 服务器: 消息 8114,级别 16,状态 5,过程 f_ip,行 6 将数据类型 nvarchar 转换为 bigint 时出错。但是单独执行如“select dbo.f_ip('81.136.124.0')”显示正确结果“测试” 您再帮我看看好吗,非常感谢!
学习 SQL Server 啊 努力 奋斗-----------------------------------------------------
-----------------------------------------------------------------------------
CREATE FUNCTION f_IP2Int(@ip char(15))
RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re= PARSENAME(@ip,4)*CAST(16777216 as bigint)
+PARSENAME(@ip,3)*65536
+PARSENAME(@ip,2)*256
+PARSENAME(@ip,1)
RETURN @re
END
GO
insert into table1 select N'137.82.0.0'
insert into table1 select N'127.0.0.1'
insert into table1 select N'127.0.0.1'
insert into table1 select N'127.0.0.1'
insert into table1 select N'137.82.0.1'
insert into table1 select N'127.0.0.1'
insert into table1 select N'84.204.124.0'
insert into table1 select N'127.0.0.1'
GO
CREATE FUNCTION f_IP2Int(@ip char(15))
RETURNS bigint
AS
BEGIN
DECLARE @re bigint
SET @re= PARSENAME(@ip,4)*CAST(16777216 as bigint)
+PARSENAME(@ip,3)*65536
+PARSENAME(@ip,2)*256
+PARSENAME(@ip,1)
RETURN @re
END
GO select dbo.f_IP2Int(IP) from table1drop table table1
drop function f_IP2Int
go
数据没有问题啊
执行select * table1 where field2 is null
符合条件的记录如下field1字段值:
137.82.0.0
127.0.0.1
127.0.0.1
127.0.0.1
137.82.0.1
127.0.0.1
84.204.124.0
127.0.0.1
按照你的函数,执行下面语句:
update table1 set field2=dbo.f_ip(field1) where field2 is null
报错:
服务器: 消息 8114,级别 16,状态 5,过程 f_ip,行 6
将数据类型 nvarchar 转换为 bigint 时出错。但是单独执行如“select dbo.f_ip('81.136.124.0')”显示正确结果“测试”
您再帮我看看好吗,非常感谢!