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

nosliwde99
nosliwde99 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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 ChongSoftware Team Lead

Commented:
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

Senior Developer
Commented:
The only way to do this in native T-SQL is dynamic SQL.

CAVEAT: This is a serious security issue.

The only two ways to evaluate expressions like this is to stuff it into dynmic SQL. Either as SELECT or SET. E.g.

DECLARE @TestTable TABLE (
    Seq INT IDENTITY ,
    StringData NVARCHAR(MAX)
);

INSERT INTO @TestTable
VALUES ( N'N''>'' + NCHAR(160) + N''<''' ); --non-breaking space

DECLARE @Result NVARCHAR(MAX);
DECLARE @Statement NVARCHAR(MAX);

SELECT TOP ( 1 ) @Statement = N' SET @Result = ' + TT.StringData + N';'
FROM   @TestTable TT
WHERE  TT.Seq = 1;

EXECUTE sys.sp_executesql @Statement ,
                          N'@Result NVARCHAR(MAX) OUTPUT' ,
                          @Result = @Result OUTPUT;

SELECT Seq ,
       StringData ,
       '>' + CHAR(160) + '<' AS Direct_Select__Desired_Results ,
       @Statement ,
       @Result
FROM   @TestTable;

Open in new window


Capture.PNG
But you cannot encapsulate the sp_executesql call into a function like your EVALUATE_STRING().

Depending on your needs: Create SQL CLR function doing this string evaluation in .NET. Or you search for it, there are commercial as also freeware solutions out there.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial