Substring Function in SQL Server

[arunkumars]

select SUBSTRING('hello 世田谷区経堂世田谷区経堂 ',5,10) as Japanese
gives output as "o ????????".

Is there any way i can get the substring of above string ?
Thanks in advance for your help.

[arunkumars]

Found this myself, but still need some help

SUBSTRING(N’世田谷区経堂世田谷区経堂’,5,6) will give the expected result, but since am writing a function to handle such cases, am not able to get this declared variables.
Ex : Substring(N'['+@Temp+']').
How do i use N here ?
Any help appreciated.

[h4ng4r18]

@Temp is an NVarChar?

Is this part of a Dynamic Sql Statement?

Please can you supply more of the query

[arunkumars]

DECLARE @TEMP NVARCHAR(100)
SET @TEMP = 'hello 世田谷区経堂世田谷区経堂'
SET @TEMP = 'N'''+@TEMP
Select SUBSTRING(@TEMP,5,10)

Am just trying to pass the variable @TEMP to substring, but doesnt work,

actually am trying this in a scalar valued function to handle such cases.

[Fenrir()]

Click Here

This article should help you understand the problem.

with the current collation settings on your database the above query should return llo ??????

[arunkumars]

Collation by default for the column is LatinSQL, but since this column data may be any language, changing this would not be an option.
am not able to concatenate N and @TEMP, this is my issue now.

[h4ng4r18]

At the risk of being unpopular - I would take a look here:

http://msdn.microsoft.com/en-GB/library/ms143726.aspx

I hope this answers your question