What is ParseName And How to split IP Addresses Using ParseName T-SQL Function
MS SQL Server uses four part object naming syntax for representing its database objects.
This syntax can be shown shortly in the form as ServerName.DatabaseName.OwnerName.ObjectName.
If you are naming an object from a database linked to the current database instance, then the ServerName represents the linked server name.
The ParseName T-SQL function can be used to fetch the pieces of the object name.
The syntax for the ParseName T-SQL function is as follows.
PARSENAME ( 'object_name' , object_piece )
'object_name' argument is the name of the database object which we are splitting to retrieve specified parts of it. The parameter type is sysname for this argument.
And 'object_piece' is the desired object part. Its type is int. The values that the object_piece argument can have are :
1 = Object name
2 = Schema name
3 = Database name
4 = Server name
DECLARE @name nvarchar(50)
SET @name = N'RemoteInstance.Northwind.dbo.Employees'
SELECT
ParseName(@name,1) AS 'Object Name',
ParseName(@name,2) AS 'Owner Name',
ParseName(@name,3) AS 'Database Name',
ParseName(@name,4) AS 'Server Name'
IP Addresses
The format of an IP address is a 32-bit numeric address written as four numbers separated by periods.
This format is very suitable for using the ParseName with IP related processes.
Now, let's see how we can use ParseName function to split IP addresses.
First we can start by getting your IP address. We will use the xp_cmdshell extended stored procedure for this task.
EXEC master..xp_cmdshell 'ipconfig'
To run the xp_cmdshell extended procedure successfully, you should have the sa user rights on your database.
And this extended stored procedure should be enabled and installed on your database.
For security reasons some database administrators might have removed xp_cmdshell from their database systems.
Also note that xp_cmdshell is disabled by default for SQL Server 2005 for security reasons.
You can read the article How to enable xp_cmdshell extended stored procedure for Microsoft SQL Server 2005 for enabling the xp_cmdshell extended procedure.
OK, I got my IP address: 192.168.1.100
declare @name nvarchar(50)
set @name = N'192.168.1.100'
SELECT
ParseName(@name,1),
ParseName(@name,2),
ParseName(@name,3),
ParseName(@name,4)
For instance if you are keeping IP addresses of uses who have posted messages for your forum application, you can use the below sql statement on your database.
SELECT
IPAddress,
ParseName(IPAddress,1),
ParseName(IPAddress,2),
ParseName(IPAddress,3),
ParseName(IPAddress,4)
FROM cs_Posts
And you can filter,group or sort your records according to the parts seperated with periods.
SELECT * FROM cs_Posts WHERE ParseName(IPAddress,4) = '127'
SELECT ParseName(IPAddress,4), Count(*) FROM cs_Posts GROUP BY ParseName(IPAddress,4)
SELECT IPAddress, * FROM cs_Posts
ORDER BY ParseName(IPAddress,4), ParseName(IPAddress,3), ParseName(IPAddress,2), ParseName(IPAddress,1)
Also these samples shows how to break up IP address data to group by subnet
If you are working with phone numbers, you can benefit from the ParseName by replacing the delimeters with periods.
declare @name nvarchar(50)
set @name = N'(222)626-8050'
set @name = REPLACE(REPLACE(REPLACE(@name, '(', '.'), ')', '.'), '-', '.')
SELECT
ParseName(@name,3) AS AreaCode,
ParseName(@name,2) + ParseName(@name,1) AS PhoneNumber
In short, SQL function ParseName can be a trick during your tasks which needs practical solutions for some the problems.