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 ',' + ''
+ ( 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
--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:
"??"
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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 ',' + ''
+ ( 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
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply