May 16, 2019 at 11:53 am
Hi Team,
i want to avoid "??" in the result set but i am getting "??"
can you please help on this.
My Script:
AllCountryStates
(
CountryName NVARCHAR(100),
Treeid int
)
insert INTO #AllCountryStates
select N'??',1
select * from #AllCountryStates
declare @Country NVARCHAR(1000)
SELECT @Country = STUFF(
(SELECT ',' + ''
FROM (select c.CountryName,c.TreeId
FROM #AllCountryStates c
) as CountryName order by TreeId
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'' )
---Country End
select @Country
--Existing Result:
Expected result:
May 16, 2019 at 2:01 pm
Please note that I hit 'Report' when I meant to hit 'Quote' and there seems to be no way of reversing that ... apologies.
This makes no sense to me - your expected result is the same as the existing result and therefore no change is required:
Existing Result:
"??"
Expected Result:
"??"
May 16, 2019 at 2:07 pm
CREATE table #AllCountryStates
(
CountryName NVARCHAR(100),
Treeid int
)
insert INTO #AllCountryStates
select N'??',1
select * from #AllCountryStates
declare @Country NVARCHAR(1000)
SELECT @Country = STUFF(
(SELECT ',' + ''
FROM (select c.CountryName,c.TreeId
FROM #AllCountryStates c
) as CountryName order by TreeId
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'' )
---Country End
select @Country
May 16, 2019 at 2:36 pm
Change this, FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'' ), to use NVARCHAR(MAX) in place of the VARCHAR(MAX). The implicit change is causing you to lose the UNICODE values.
May 16, 2019 at 2:50 pm
CREATE table #AllCountryStates ( CountryName NVARCHAR(100), Treeid int ) insert INTO #AllCountryStates select N'??',1 select * from #AllCountryStates declare @Country NVARCHAR(1000) SELECT @Country = STUFF( (SELECT ',' + '' + ( CountryName ) + '' FROM (select c.CountryName,c.TreeId FROM #AllCountryStates c ) as CountryName order by TreeId FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'' ) ---Country End select @Country
Your problem is here:
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'' )
Your country names are NVARCHAR, but you are converting them to VARCHAR, so you are losing information that cannot be encoded in VARCHAR.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy