ParseName Function

Question

I am using the select statement : "select PARSENAME('HONG, ELSIE W..9110',2)"

For parameter 1(select PARSENAME('HONG, ELSIE W..9110',1) it is returning 9110 which is what I am expecting
For parameter 2(select PARSENAME('HONG, ELSIE W..9110',2) it is returning NULL , but I am expecting to return 'HONG, ELSIE W.'

Data(9110) after last dot should return for param 1 and data('HONG, ELSIE W.') before the last dot should return for param 2.

Answer 1

Hi @Rahul Polaboina
As experts explained above, PARSENAME just returns the specified part of the specified object name which is [Server name].[Database name].[Schema name].[Object name] separated by .
(1 = Object name; 2 = Schema name; 3 = Database name; 4 = Server name; )
Note: Each part of the 'object_name' string is type sysname which is equivalent to nvarchar(128) or 256 bytes. If any part of the string exceeds 256 bytes, PARSENAME will return NULL for that part as it is not a valid sysname.

but I am expecting to return 'HONG, ELSIE W.'

If you want to include . in your desired result, PARSENAME is not a good choice.
Try this:

DECLARE @STRING VARCHAR(200)='HONG, ELSIE W..9110'  
  
SELECT LEFT(@STRING,LEN(@STRING)-CHARINDEX('.',REVERSE(@STRING)))  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

No, your expectations are wrong.

I don't know for what purpose you are using parsename, but the intended purpose is to parse object names in SQL Server. Such names can have up to four components separated by periods. SQL Server permits you to leave out all components but the last. And, yes, you can leave out one in the middle. It is not at all uncommon to say:

   SELECT col FROM ThatDB..ThisTbl  

and leaving out the schema. The schema will be the default schema for the current user in ThatDB, and in many cases that will be dbo.

Thus, if you run:

   select PARSENAME('HONG, ELSIE W..9110',3)  

You will get back HONG, ELSIE W.

It is not uncommon to use parsename for other purposes, but it is essential to understand what it is intended for, and you need to be dead sure that your case fits into these limitations. For instance, this does not work out:

   SELECT parsename('a.b.c.d.e', 1)  

Because this is not a legal object specification, you get NULL back.