Ordering the String Value possibly by using PARSENAME function

Question

Ordering the String Value possibly by using PARSENAME function

farooq.hbs

SSCrazy

Points: 2083
More actions
September 24, 2014 at 7:16 am

#290230

Hi,
If the string contain more than 4 delimiter, it returns null..
What the way to fix this,
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')
SELECT *, CASEWHEN [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 ,
PARSENAME(IPAddress,4) AS pointCoumn FROM @WorkStation

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

farooq.hbs (9/24/2014)
Hi,
If the string contain more than 4 delimiter, it returns null..
What the way to fix this,
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')
SELECT *, CASEWHEN [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 ,
PARSENAME(IPAddress,4) AS pointCoumn FROM @WorkStation

That is because PARSENAME is designed to split apart sql object names which is 4 parts. It is not designed as a string splitter. If you use the DelimitedSplit8K function (found in the article referenced in my signature about splitting strings) you could make your query something like this.

select w.IPAddress

, s.Item

from @WorkStation w

cross apply dbo.DelimitedSplit8k(IPAddress, '.') s

where s.ItemNumber = 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

farooq.hbs SSCrazy Points: 2083 More actions · Answer 2

farooq.hbs

SSCrazy

Points: 2083

September 24, 2014 at 8:16 am

#1747851

Thank you!

But we are not suppose to use functions.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

farooq.hbs (9/24/2014)
Thank you!
But we are not suppose to use functions.

That's going to be a problem since PARSENAME is a function. is it really such a broad exclusion, because that's a fairly ridiculous requirement (it's almost impossible to do anything without some usage of functions).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

farooq.hbs (9/24/2014)
Thank you!
But we are not suppose to use functions.

But PARSENAME is a function. :hehe:

Even if you could decide to use the DelimitedSplit8k function, I guess it would be unnecessary work.

A LEFT could work better.

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 *, CASEWHEN [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 ,

PARSENAME(IPAddress,4),

LEFT(IPAddress,CHARINDEX('.', IPAddress) - 1) AS pointCoumn FROM @WorkStation

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

You could adjust it to handle an address without a period / with only the top address specified (such as "10").

LEFT(IPAddress,CHARINDEX('.', IPAddress + '.') - 1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

farooq.hbs SSCrazy Points: 2083 More actions · Answer 6

Thanks!!

But i am not getting the desired result.

Basically i want to order the (SequToParse) field value.. If you could check the data then you will notice there is no proper ordering in the query.

So i used predefined PARSENAME function to order the value. Since this funtion takes only upto 4 sequence, its not giving my result.

Check this query,