Splitting String Using SQL Statement (Ip Address)

How to split an IP Address string in DB2 SQL (for converting to IP Number)?

I've not found anything cleaner and simpler than this, but the following SQL will do the job.

SELECT
    LO.IPAddress
    16777216 * CAST(LEFT(LO.IPAddress, LOCATE('.', LO.IPAddress, 1)-1) AS BIGINT)
    +  65536 * CAST(SUBSTR(LO.IPAddress, LOCATE('.', LO.IPAddress, 1) + 1, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) - LOCATE('.', LO.IPAddress, 1) - 1) AS BIGINT)
    +    256 * CAST(SUBSTR(LO.IPAddress, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) + 1, LOCATE('.', LO.IPAddress, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) +1) - LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) - 1) AS BIGINT) 
    +          CAST(RIGHT(LO.IPAddress, LENGTH(LO.IPAddress) - LOCATE('.', LO.IPAddress, LOCATE('.', LO.IPAddress,LOCATE('.', LO.IPAddress, 1) +1) +1)) AS BIGINT)
    AS IPNumber

FROM Login LO

Looking for a more efficient way to break IP addresses into octets

This Tip-of-the-day (and this one) describes how to use the T-SQL PARSENAME function
to separate IP addresses as well as 4-part database object names.

DECLARE @IPAddresses TABLE ( [IPAddress] VARCHAR(20))

INSERT INTO @IPAddresses VALUES ('10.0.0.1')
INSERT INTO @IPAddresses VALUES ('255.255.255.255')
INSERT INTO @IPAddresses VALUES ('192.123.545.12')
INSERT INTO @IPAddresses VALUES ('1.2.3.4')

SELECT * FROM @IPAddresses
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
         CAST(PARSENAME([IPAddress], 3) AS INT),
         CAST(PARSENAME([IPAddress], 2) AS INT),
         CAST(PARSENAME([IPAddress], 1) AS INT)

IPAddress
----------------
1.2.3.4
10.0.0.1
192.123.545.12
255.255.255.255

sql split string based on delimiter with varying numbers

Use a combination of SUBSTRING, CHARINDEX & LEN.

SELECT yourfield,
SUBSTRING(yourfield, 1,CHARINDEX('-', yourfield)-1) LEFTSIDE,
SUBSTRING(yourfield, CHARINDEX('-', yourfield)+1, LEN(yourfield)) RIGHTSIDE
FROM yourtable

Output

yourfield                   LEFTSIDE      RIGHTSIDE
10.159.101.0-10.159.101.255 10.159.101.0  10.159.101.255

SQL Fiddle: http://sqlfiddle.com/#!6/cb36f/3/0

Splitting address

you can create this function:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       CHAR_LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, "");

query:

select distinct
            OWNER,prod_id,SPLIT_STR(ADDRESS,' ',1) as pos1 ,SPLIT_STR(ADDRESS,' ',2) as pos2,
SPLIT_STR(ADDRESS,' ',3) as pos3 ,SPLIT_STR(ADDRESS,' ',4) as pos4,SPLIT_STR(ADDRESS,' ',5) as pos5
from vw_EMPLOYEE

---

Related Topics