CODEWITHSUNDEEP
sqlsql-server-2008t-sql
svm
svm

Reputation: 5

how can sum N char with variable in tsql?

how can sum N char with variable in tsql?

this sql query return correct result

select * from PersonsInfoTbl where Name LIKE  N'علی%';

result image

this sql query return incorrect result

declare @Name nvarchar(50)
set @Name = 'علی';
select * from PersonsInfoTbl where Name LIKE  N''+@Name+'%';

result image

My database is Microsoft SQL Server 2008 R2

Upvotes: 0

Views: 156

Answers (3)

Bacon Bits
Bacon Bits

Reputation: 32145

You must prefix any nvarchar string literal with an N. If you don't do that, SQL Server will assume that it's not an nvarchar.

For example:

declare @Name1 nvarchar(50), @Name2 nvarchar(50)

set @Name1 = N'علی';
set @Name2 = 'علی';

select @Name1 "Name1", @Name2 "Name2"

Will return this:

 Name1     Name2    
 --------  -------- 
 علی       ???

Try this:

declare @Name nvarchar(50)
set @Name = N'علی';
select * from PersonsInfoTbl where Name LIKE @Name + N'%';

Upvotes: 2

Jacob H
Jacob H

Reputation: 2505

Add another variable to store the SQL command as a string. Also you need to use the unicode N on your variables and commands so that text is also stored as unicode.

declare @Name nvarchar(50)
declare @sqlcmd nvarchar(max)
set @Name = N'علی';
set @sqlcmd = N'select * from PersonsInfoTbl where Name LIKE  '''+@Name+'%'''
EXECUTE(@sqlcmd)

Edit: I suppose the dynamic part is not necessary in this specific case, but you will find this useful in writing queries in the future.

Upvotes: 0

Kevin
Kevin

Reputation: 781

the problem you set the variable @Name without the N'', this is nessecary for sql to know you set it as nvarchar

declare @Name nvarchar(50)
set @Name = N'علی';
select * from @t where Name LIKE  N''+@Name+'%';

this will return the rows

Upvotes: 0

Related Questions