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
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.