I have a value (500 , 850 , 65.5) as GivenUnitPrice and I want to separate these by making separate columns by PARSENAME
I have tried like this
PARSENAME(GivenUnitPrice, 3) as DB,
PARSENAME(GivenUnitPrice, 2) as Owner,
PARSENAME(GivenUnitPrice, 1) as Object
and the result is
DB | Owner | Object
NULL | 500 , 850 , 65 | 5
4 Answers
Seems you want to seperate the characters by commas but Parsename() function splits by dot character( e.g. decimal number 65.5 is also splitted as if seperate integers ), so yields wrong results for your case. It's better to use replace(),substring() and charindex() together as :
with t as
(
select replace(replace('500 , 850 , 65.5',')',''),'(','') as GivenUnitPrice
), t2 as
(
select substring(GivenUnitPrice,1,charindex(',',GivenUnitPrice)-1) as db,
substring(GivenUnitPrice,charindex(',',GivenUnitPrice)+1,len(GivenUnitPrice)) as owner_object
from t
)
select db,
substring(owner_object,1,charindex(',',owner_object)-1) as owner,
substring(owner_object,charindex(',',owner_object)+1,len(owner_object)) as object
from t2;
db owner object
500 850 65.5
DECLARE @GivenUnitPrice VARCHAR(100)= '500 , 850 , 65.5'
SELECT PARSENAME(replacE(@GivenUnitPrice,',','.'),4) as DB,PARSENAME(replacE(@GivenUnitPrice,',','.'),3) as Owner,PARSENAME(replacE(@GivenUnitPrice,',','.'),2)+ '.'+PARSENAME(@GivenUnitPrice,1) AS OBJECT
You can try this....
DECLARE @UnitPrice VARCHAR(100)
SET @UnitPrice= '500 , 850 , 65.5'
SELECT PARSENAME(REPLACE(@UnitPrice,',','.'),4) as DB,
PARSENAME(REPLACE(@UnitPrice,',','.'),3) as Owner,
PARSENAME(REPLACE(@UnitPrice, ',', '.'), 2) AS OBJECT,
PARSENAME(REPLACE(@UnitPrice, '', '.'), 1) AS OBJECT
PARSENAME basically consider .(Dot) as a delimeter in the string. In your input string, there are only one .(Dot) available at the end and as a result you are getting values only at Owner and Object column. IF you want to use PARSENAME for this string, please replace commas with .(Dot) first and then apply PARSENAME as below-
DECLARE @ObjectName NVARCHAR(1000);
SET @ObjectName = '500 , 850 , 65.5';
SET @ObjectName = REPLACE(@ObjectName, ',', '.');
SELECT PARSENAME(@ObjectName, 4) AS Server,
PARSENAME(@ObjectName, 3) AS DB,
PARSENAME(@ObjectName, 2) AS Owner,
PARSENAME(@ObjectName, 1) AS Object;
