Avatar of nosliwde99
nosliwde99
 asked on

"Evaluate" a string (assembled) that is returned from a table

I'm looking for a way to "evaluate" an assembled string that is stored in a table.
The table has string data with many types of special characters such as nchar(160) or nchar(8204).  Many columns of it in many rows.
As a simple example let's take '>' + char(160) + '<' as the string data, and I want it to return "> <".

Looking for something that would be akin to SELECT EVALUATE_STRING(StringData) FROM Table.

DECLARE @TestTable TABLE(Seq INT IDENTITY, StringData nvarchar(100))
INSERT INTO @TestTable VALUES ( '''>'' + char(160) + ''<''' )  --non-breaking space

SELECT Seq, StringData 
	FROM @TestTable

--Shows the desired result when not selected from a table
SELECT '>' + char(160) + '<' AS 'Direct_Select__Desired_Results' 

--If I try a select statement, it doesn't work.
SELECT Seq, (SELECT StringData) AS 'Subqueried_Data' 
	FROM @TestTable

--How do I get the desired results when selecting from a table?  
SELECT Seq, /*??*/ StringData AS '??Evaluated_StringData??' 
	FROM @TestTable

Open in new window

Suggestions?  I hope it's simple.

Thanks.

SQL Server 2008
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
ste5an

8/22/2022 - Mon
Raja Jegan R

Try changing your INSERT statement to this to get it work..
Basically ''' will add 1 single quote ' into your string and hence you are getting the additional quotes..
INSERT INTO @TestTable VALUES ( '>' + char(160) + '<' )  --non-breaking space

Open in new window

Ryan Chong

you may try use a temp table instead:

IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable

create TABLE #TestTable(Seq INT IDENTITY, StringData nvarchar(100))
INSERT INTO #TestTable VALUES ( '''>'' + char(160) + ''<''' )  --non-breaking space

SELECT Seq, StringData FROM #TestTable

Declare @value varchar(100)
Select @value = StringData from #TestTable
Declare @sql nvarchar(max) = 'SELECT Seq, StringData, '+@value+' AS ''Subqueried_Data'' FROM #TestTable '
EXEC sp_executesql @sql

Open in new window

ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23