盒子论坛 v2.1

对IP字段，按数字排序，在 SQL Server 里如下写法：
select *
from IPList
order by cast(parsename(ip,4) as int),cast(parsename(ip,3) as int),cast(parsename(ip,2) as int),cast(parsename(ip,1) as int)

请帮忙改成 SQLite 的，谢谢！！
----------------------------------------------
-

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/7/16 5:03:08

1楼：

I dont understand very well, but...

https://docs.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-ver15

"Arguments
'object_name' Is the parameter that holds the name of the object for which to retrieve the specified object part. This parameter is an optionally-qualified object name. If all parts of the object name are qualified, this name can have four parts: the server name, the database name, the schema name, and the object name. Each part of the 'object_name' string is type sysname which is equivalent to nvarchar(128) or 256 bytes. If any part of the string exceeds 256 bytes, PARSENAME will return NULL for that part as it is not a valid sysname.

object_piece
Is the object part to return. object_piece is of type int, and can have these values:
1 = Object name
2 = Schema name
3 = Database name
4 = Server name
"

SQL

Copy
-- Uses AdventureWorks

SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 1) AS 'Object Name';
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 2) AS 'Schema Name';
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 3) AS 'Database Name';
SELECT PARSENAME('AdventureWorksPDW2012.dbo.DimCustomer', 4) AS 'Server Name';
GO

Here is the result set.

Copy
Object Name
----------
DimCustomer

(1 row(s) affected)

Schema Name
----------
dbo

(1 row(s) affected)

Database Name
----------
AdventureWorksPDW2012

(1 row(s) affected)

Server Name
----------
(null)

(1 row(s) affected)

---------- my note ----------
in your Database you would can use 4 fields (Byte type) to store each part of IP4, or 1 Field (String type) with the IP whole. Or, a field (String type) with Hexa value for each IP4.

For IP6, the same idea with size necessary for each part, of course!
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/7/16 5:07:42

2楼：

does some sample stored in your MS SQL Database for analize?

table: IPList:
----------
IP field
xxxxx <---- What is store in each row of the table? what is the info?
xxxxx
xxxxx
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/7/16 5:14:09

3楼：

maybe help using INET_ATON() SQLite function?

https://stackoverflow.com/questions/1270855/what-is-the-sql-server-equivalent-of-inet-aton

see the "Stored Procedure" above:

CREATE FUNCTION dbo.ipStringToInt
(
    @ip CHAR(15)
)
RETURNS INT
AS
BEGIN
    DECLARE @rv INT,
        @o1 INT,
        @o2 INT,
        @o3 INT,
        @o4 INT,
        @base INT

    SELECT
        @o1 = CONVERT(INT, PARSENAME(@ip, 4)),
        @o2 = CONVERT(INT, PARSENAME(@ip, 3)),
        @o3 = CONVERT(INT, PARSENAME(@ip, 2)),
        @o4 = CONVERT(INT, PARSENAME(@ip, 1))

    IF (@o1 BETWEEN 0 AND 255)
        AND (@o2 BETWEEN 0 AND 255)
        AND (@o3 BETWEEN 0 AND 255)
        AND (@o4 BETWEEN 0 AND 255)
    BEGIN
        SELECT @base = CASE
          WHEN @o1 < 128 THEN
          (@o1 * 16777216)
          ELSE
          -(256 - @o1) * 16777216
          END

        SET @rv = @base +
          (@o2 * 65536) +
          (@o3 * 256) +
          (@o4)
    END
    ELSE
        SET @rv = -1
    RETURN @rv
END

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

INSERT mytable VALUES(dbo.ipStringToInt('1.2.3.4'))

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

CREATE FUNCTION dbo.ipIntToString
(
    @ip bigINT
)
RETURNS CHAR(15)
AS
BEGIN
    DECLARE @o1 bigINT,
        @o2 bigINT,
        @o3 bigINT,
        @o4 bigINT

    IF ABS(@ip) > 4294967295
        RETURN '255.255.255.255'

    SET @o1 = @ip / 16777216

    IF @o1 = 0
        SELECT @o1 = 255, @ip = @ip + 16777216

    ELSE IF @o1 < 0
    BEGIN
        IF @ip % 16777216 = 0
          SET @o1 = @o1 + 256
        ELSE
        BEGIN
          SET @o1 = @o1 + 255
          IF @o1 = 128
          SET @ip = @ip + 2147483648
          ELSE
          SET @ip = @ip + (16777216 * (256 - @o1))
        END
    END
    ELSE
    BEGIN
        SET @ip = @ip - (16777216 * @o1)
    END

    SET @ip = @ip % 16777216
    SET @o2 = @ip / 65536
    SET @ip = @ip % 65536
    SET @o3 = @ip / 256
    SET @ip = @ip % 256
    SET @o4 = @ip

    RETURN
        CONVERT(VARCHAR(4), @o1) + '.' +
        CONVERT(VARCHAR(4), @o2) + '.' +
        CONVERT(VARCHAR(4), @o3) + '.' +
        CONVERT(VARCHAR(4), @o4)
END
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/7/16 23:55:23

4楼：

搞定了：
      SQL.Add('select IP, Name, Typ, Status');
      SQL.Add(Format(',to_number(substr(ip,1,instr(ip,%0:s%1:s%0:s,1,1)-1)) as ip1',['''','.']));
      SQL.Add(Format(',to_number(substr(ip,instr(ip,%0:s%1:s%0:s,1,1)+1,instr(ip,%0:s%1:s%0:s,1,2)-instr(ip,%0:s%1:s%0:s,1,1)-1)) as ip2',['''','.']));
      SQL.Add(Format(',to_number(substr(ip,instr(ip,%0:s%1:s%0:s,1,2)+1,instr(ip,%0:s%1:s%0:s,1,3)-instr(ip,%0:s%1:s%0:s,1,2)-1)) as ip3',['''','.']));
      SQL.Add(Format(',to_number(substr(ip,instr(ip,%0:s%1:s%0:s,1,3)+1,length(ip)+1-instr(ip,%0:s%1:s%0:s,1,3))) as ip4',['''','.']));
      SQL.Add('from   IPList');
      SQL.Add('order by ip1, ip2, ip3, ip4'); //SQLite/Oracle 对 IP 按数字排序
----------------------------------------------
-

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/7/17 3:33:27

5楼：

我并没有真正理解你上面的“SQL”，因为我不是每天都使用“MSSQL/MySQL”...

然而，为了在“SQL”语言中更有效地使用“ORDER BY/INDEX”，我更喜欢只有一个字段。也就是说，例如，由于您可以使用唯一的数值（整数或 BigInt / Int64）获得“IP”，因此您不需要生成复合索引（ ORDER BY ip1, ip2, ip3, ip4 ），但只是一个“ORDER BY MyIPnumericValue”，它将是 IP 地址的值，但是，采用整数格式（Integer / BigInt 或 Int64 甚至 HEXA）。 - 由于数值代表IP字符串值，其中ip-integer-greater = ip-string-greater

1.1.1.1 = 16843009
255.255.255.255 = 4294967295

所以我认为你的“INDEX”“SQL”会更有效地使用。

这个想法怎么样？
----------
I didn't really understand your "SQL" above, because I don't use "MSSQL / MySQL" on a daily basis...

However, for a more performative use of "ORDER BY/INDEX" in the "SQL" language, I prefer to have only one field. That is, since you can have your "IP" with a unique numeric value (an Integer or BigInt / Int64), for example, then you would not need to produce a composite index ( ORDER BY ip1, ip2, ip3, ip4 ), but simply an "ORDER BY MyIPnumericValue", where it would be the value of the IP address, however, in integer format (Integer / BigInt or Int64 or even in HEXA). - Since the numeric value represents the IP string value, where ip-integer-greater = ip-string-greater

1.1.1.1 = 16843009
255.255.255.255 = 4294967295

So I think your "INDEX" "SQL" would be more efficient to use.

How about the idea? Or, some like this...

SELECT
ipAddress {xxx.xxx.xxx.xxx}.
Name,
Typ,
Status,
MyFuncReturnIPinFormatNumericOrString as IPtoOrder
FROM
IpList
ORDER BY IPtoOrder
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

作者：

dalas (dalas)	★☆☆☆☆	-
	普通会员

2021/7/17 8:19:12

6楼：

我上面的代码是放到 TQuery 里执行的 Delphi 代码，实际执行的 SQL 脚本如下：
select IP, Name, Typ, Status
,to_number(substr(ip,1,instr(ip,'.',1,1)-1)) as ip1
,to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2)-instr(ip,'.',1,1)-1)) as ip2
,to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3)-instr(ip,'.',1,2)-1)) as ip3
,to_number(substr(ip,instr(ip,'.',1,3)+1,length(ip)+1-instr(ip,'.',1,3))) as ip4
from IPList
order by ip1, ip2, ip3, ip4
;

原理其实就是把 IP 拆分成4位数字，再按数字排序。
你上面的代码，也是一个不错的办法。
----------------------------------------------
-

作者：

emailx45 (emailx45)	▲▲▲▲△	-
	普通会员

2021/7/18 5:13:59

7楼：

try my code
http://bbs.2ccc.com/topic.asp?topicid=615219
----------------------------------------------
The higher the degree, the greater the respect given to the humblest!RAD 11.3

信息

登陆以后才能回复