SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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'

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:

declare @name nvarchar(50)
set @name = N''


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.

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, '(', '.'), ')', '.'), '-', '.')

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.