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'
GO
|
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'
GO
|
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" at
http://www.kodyaz.com/content/xpcmdshell.aspx 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)
GO
|
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
GO
|
In short, SQL function ParseName can be a trick during your tasks which needs
practical solutions for some the problems.