Geek asks and answers

SQL Server changed my rows before running a query

I am trying to select some special records containing special characters, but SQL Server changes its string characters before executing the query.

For instance:

    DECLARE @param NVARCHAR(30)

    SET @param=N'€÷þ'--this is my special string that i want to be searched exactly.

    DECLARE @TSQL varchar(8000)
    SET @TSQL = 'SELECT * FROM MyTable WHERE MyFieldName LIKE %' + @param + '% '

    PRINT @TSQL

    --EXECUTE (@TSQL)

But as a result (print) I see:

SELECT * FROM MyTable WHERE MyFieldName LIKE '%€÷þ?%'

As you can see, what part of the string is converted to a character (?), This problem causes my command to SELECTreturn null.

I am trying to change the sorting of the database for which I am running a query:

SQL_Latin1_General_CP1_CI_AS

It works fine with some special string, but also does not support all my lines. So the question here is: how can I tell SQL Server, please do not change my ascii strings? Is there any way (or any mapping) to tell SQL Server that sees the row exactly as it really is?

PS: SQL Server 2008 R2.

+5
2

, , Unicode NVARCHAR VARCHAR - .....

DECLARE @param NVARCHAR(30)
SET @param = N'€÷þ'--this is my special string that i want to be searched exactly.

DECLARE @TSQL NVARCHAR(4000)    -- <=== use NVARCHAR here
SET @TSQL = N'SELECT * FROM MyTable WHERE MyFieldName LIKE %' + @param + N'% '

PRINT @TSQL

, ....

: SQL-, , - SQL-. sp_executesql, .

+2
DECLARE @TSQL varchar(8000)

varchar(8000) €÷þ. , , @param; - NVARCHAR.

usr , sp_executesql . :

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2012.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
+2

More articles: