September 24, 2014 at 5:59 am
Hi,
I want to order the VARCHAR datatype column which has parent-child hierarchical concept.
I tried with many different logic but couldn't find a desired solution.
I know it is not formatted as it be on this forum but please help on this
Does PARSENAME function works only upto 4 values.
For example:
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)
If the string has more than 5 then it does not sorted..
Please advice.
September 24, 2014 at 11:03 am
What order do you want your example data to be returned in?
If I just use:
SELECT
*
FROM
@IPAddresses
ORDER BY
[@IPAddresses].IPAddress
I get the same order as you are getting using PARSENAME.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2014 at 6:42 am
The problem is with this below query,
DECLARE @WorkStation TABLE ( [IPAddress] NVARCHAR(100) )
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('255.255.0.0')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('98.123.251.21')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('192.120.40.243')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('207.46.199.60')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('10.0.0.1')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('68.142.197.0')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('255.255.255.255')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('65.54.152.142')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('64.233.188.15')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('65.97.176.172')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('64.233.188.10.15')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('65.97.176.11.172')
INSERT INTO @WorkStation ( [IPAddress] ) VALUES ('11.172')
SELECT * FROM
(SELECT *, CASE
WHEN [IPAddress] LIKE '%.%.%.%.%' THEN [IPAddress]
WHEN [IPAddress] LIKE '%.%.%.%' THEN [IPAddress] +'.0'
WHEN [IPAddress] LIKE '%.%.%' THEN [IPAddress] + '.0.0'
WHEN [IPAddress] LIKE '%.%' THEN [IPAddress] + '.0.0.0'
ELSE [IPAddress] + '.0.0.0.0'
END AS sequToParse
FROM @WorkStation) AS data
ORDER BY
CAST(PARSENAME([IPAddress], 5) AS INT),
CAST(PARSENAME([IPAddress], 4) AS INT),
CAST(PARSENAME([IPAddress], 3) AS INT),
CAST(PARSENAME([IPAddress], 2) AS INT),
CAST(PARSENAME([IPAddress], 1) AS INT)
Here it is not ordering.
Upto 4 decimal places it is ordering and if it goes beyond 4 decimal then it is not ordering
September 25, 2014 at 7:20 am
I'm sure there are other, perhaps simpler ways to do this, but here's one that works using Jeff Moden's DelimiteSplit8K function which you can get from and learn about here[/url]:
DECLARE @WorkStation TABLE
(
[IPAddress] NVARCHAR(100)
);
INSERT INTO @WorkStation
([IPAddress])
VALUES
('255.255.0.0');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('98.123.251.21');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('192.120.40.243');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('207.46.199.60');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('10.0.0.1');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('68.142.197.0');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('255.255.255.255');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('65.54.152.142');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('64.233.188.15');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('65.97.176.172');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('64.233.188.10.15');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('65.97.176.11.172');
INSERT INTO @WorkStation
([IPAddress])
VALUES
('11.172');
WITH splitIP
AS (
SELECT
WS.IPAddress,
DSK.ItemNumber,
CONVERT(INT, DSK.Item) AS Item
FROM
@WorkStation AS WS
CROSS APPLY dbo.DelimitedSplit8K(WS.IPAddress, '.') AS DSK
),
theData
AS (
SELECT
IPAddress,
[1],
[2],
[3],
[4],
[5]
FROM
(
SELECT
*
FROM
splitIP
) AS source PIVOT( MIN(Item) FOR ItemNumber IN ([1], [2], [3],
[4], [5]) ) AS theData
)
SELECT
theData.IPAddress
FROM
theData
ORDER BY
[1],
[2],
[3],
[4],
[5];
You could accomplish the same thing with SUBSTRING and CHARINDEX to break out the IPAddress column like you are trying to do with PARSENAME, but I think splitting on the '.' and then pivoting is easier.
I'm sure there's a better way, but I don't have it currently.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2014 at 7:44 am
I'm sure this has already been suggested - anyway, here it is again:
SELECT *
FROM @WorkStation
CROSS APPLY (
SELECT Thingy = CAST(
(SELECT [text()] = RIGHT('000'+Item,3)
FROM [dbo].[DelimitedSplit8K] (IPAddress,'.')
ORDER BY ItemNumber
FOR XML PATH(''))
AS VARCHAR(100))
) x
ORDER BY Thingy
If you really can't use any user-defined functions, then you could do it like this:
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
SELECT *
FROM @WorkStation
CROSS APPLY ( -- x
SELECT Thingy = CAST(
(SELECT [text()] = RIGHT('000'+SUBSTRING(IPAddress, l.N1, l.L1),3) + '.'
FROM ( -- l
SELECT
s.N1, L1 = ISNULL(NULLIF(CHARINDEX('.',IPAddress,s.N1),0)-s.N1,8000)
FROM ( -- s
SELECT n1 = 1 UNION ALL
SELECT t.N+1
FROM ( -- t
SELECT TOP (ISNULL(DATALENGTH(IPAddress),0)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM cteTally
) t
WHERE SUBSTRING(IPAddress,t.N,1) COLLATE Latin1_General_BIN = '.'
) s
) l
ORDER BY ROW_NUMBER() OVER(ORDER BY l.N1)
FOR XML PATH(''))
AS VARCHAR(100))
) x
ORDER BY Thingy
Edit: reformatted code
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply