Home > T-SQL > SQL SERVER – The wonder of ParseName function – TSQL – @SQLServer

SQL SERVER – The wonder of ParseName function – TSQL – @SQLServer


PARSENAME(,): It returns a portion of a four-part dot delimited object. The four parts of an object are object name, owner/schema name, database name, and server name.

This function needs two parameters Object_name and object part number. Object part should be represented as an integer number 1 – object name, 2 – owner/schema name, 3 – database name, 4 – server name. It returns nchar type data for output value.

This function does not check the validity object existence in the SQL Server. Example 1 shows how to use this function in SQL Query.

Example 1:

Execute this SQL Query in the SSMS Query window

select PARSENAME(‘[dbo].[AdventureWorks2012].[HumanResources].[Department]’, 1) as ‘Object Name’,  PARSENAME(‘[dbo].[AdventureWorks2012].[HumanResources].[Department]’, 2) as ‘Schema Name’,  PARSENAME(‘[dbo].[AdventureWorks2012].[HumanResources].[Department]’, 3) as ‘Database Name’,  PARSENAME(‘[dbo].[AdventureWorks2012].[HumanResources].[Department]’, 4) as ‘Server Name’

My output

pic1

Example 2:

This example uses non-existence object in the parsename function.

My output

pic2

We can also use this function for our general purpose in our application. In the example 3, I am using this function to extract a string.

Example 3:

pic2We have learned to use ParseName function in SQL Server. So, use this function in your application when you need do something like this.

Advertisements
  1. September 14, 2013 at 1:14 am

    is there any word limit? when I use SELECT PARSENAME(REPLACE(‘Word1 word2 word3 word4’, ‘ ‘, ‘.’), 3) its ok. But when I use SELECT PARSENAME(REPLACE(‘Word1 word2 word3 word4 word5’, ‘ ‘, ‘.’), 3) it returns me null. And Is there any way to getting first word with the first parameter?

  2. September 16, 2013 at 11:37 pm

    Yes, there is a limit. Think of the IP address. It should always have 4 part number like this
    255.255.255.255. So, the string should follow the same rule here when using parsename() function.

  3. September 27, 2013 at 10:40 am

    could be more useful, why is it only for four parts is an unnecessary validation that makes the function really limited

  4. September 27, 2013 at 1:26 pm

    That’s is true. But, I think every function has its own limitations.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: