PARSENAME function in SQL Server and its uses

PARSENAME function in SQL Server and its uses

String Manipulation is one of the toughest and confusing methodologies to adopt in data retrieval process in any backend technology.

Inconsistent length of the sub-strings and the various delimiters just add to up this mess.

Striping sub-strings separated with delimiters is no more a problem.

This tip helps you out in such situations, and makes the code pretty neat.

Well, let’s assume that you have a VARCHAR field with values like ’20:44:23’ , ’44:23:12’ etc…which stand for values of (HH:MM:SS) time.

And you need to rip the Hours, Minutes and seconds part separately.

SQL Server has a function called PARSENAME.  This function has following syntax: PARSENAME(‘Object_Name’, ‘Object_Piece’)

Note: The only change that you have to make to your variable to make PARSENAME applicable that the delimeter of your variable should be a dot (.).

Let’s see how we can use it.

DECLARE @TestTime VARCHAR(50)

SET @TestTime = '12:45:09'

SET @TestTime = REPLACE(@TestTime, ':', '.')                                       /*See the note above*/

SELECT CAST(PARSENAME(@TestTime, 4) AS INT) AS 'Empty'

SELECT CAST(PARSENAME(@TestTime, 3) AS INT) AS 'Hours'

SELECT CAST(PARSENAME(@TestTime, 2) AS INT) AS 'Minutes'

SELECT CAST(PARSENAME(@TestTime, 1) AS INT) AS 'Seconds'

The Object_Name parameter takes the variable that has four parts separated by delimiters. A vast usage of this function is found for fields that hold values like IP’s of Computers. The Object_Piece denotes values from 4 down to 1. So in the above example , you can see that PARSENAME function with ‘4’ as ‘Object_Piece’) returns nothing. And then it takes 3, 2 and 1 to get the HH . MM and SS part of the variable, respectively.

By [)ia6l0 iii   Popularity  (1827 Views)