Last post Mar 16, 2015 02:26 AM by rameezshaikh9
Mar 14, 2015 06:17 AM|rameezshaikh9|LINK
I know that we can assign 'N' in front of single quotation-mark string and apply it to nvarchar sql variable and it will print me nvarchar string in same format. If I remove N or if I make variable as varchar, it will print unicode letters as '?????'
declare @String nvarchar(max) = N'सारांश;abc;pqr'
It will print 'सारांश;abc;pqr'
But, my string is coming from my front end. So, it will be stored in variable in sql. So, How can I apply this 'N' before sql variable.
Something like this????
where @String is nvarchar variable and is bringing the same string which I've written above.
Mar 14, 2015 06:46 AM|PatriceSc|LINK
N is a prefix for *literal* strings. It doesn't make sense for variable as they are typed and already known to be Nvarchar. As you said print @String does work so I'm not sure what you meant with print
N@String that doesn't work obviously.
For now it would rather some kind of problem in how you pass a value from your web server to your SQL Server database? How are you doing that? It should just work (unless you are constructing the SQL statement yourself which is bad).
It could be also an encoding problem when you are showing a value that comes from your db.
IMO for now give more details about the wrong behavior you see (in particular to make sure if the value is wrong in the db or just when shown on your web page) rather than to start already to deal with how to fix this (we need first to know exactly what
Mar 15, 2015 12:09 PM|rameezshaikh9|LINK
See, actually I am getting such string from my front end string variable. And I am assigning it to nvarchar(max) variable in sql stored procedure. Actually, I have created one function which split string by delimiter and gives records as different rows.
I have been using it for last two years or so. So, it is fine. But, now here the challenge is that this string can bring some unicode data as well.
Something like this 'सारांश;abc;pqr'
Now when I apply same function to this string it gives rows like ????? in first row, abc in second and pqr in third.
Whereas it should actually print सारांश in first row.
I tried to find solution. So, I just directly set this string to sql nvarchar variable. Obviously, it didn't work. Then, I applied N before the string and it worked. So, it is working if I set this directly to string.
But, this data is coming from front end and I can't set such string directly in my stored procedure. I have to accept it to my nvarchar variable in SP. And that is where I'm stuck. as string will be without 'N', so it will return ????? to me.
I hope you understand better this time :)
Mar 15, 2015 12:13 PM|rameezshaikh9|LINK
I hope someone answers this very soon! I have to work on this tomorrow at any cost. :)
Mar 15, 2015 12:43 PM|PatriceSc|LINK
So you are passing a value to a SP. The SP uses NVARCHAR and the underlying column uses NVARCHAR as well. Then using SqlParameter.AddWithValue with the parameter name and the value should just work.
This is why it's hard to answer. The point is that if you are using the best practice method to pass your parameter it just works.
And without knowing what you are doing there is no way to tell you have done wrong (could it be that at some point you still use VARCHAR rather than NVARCHAR for processing the incoming value?. For example do a test and save just the incoming value into
a test table with a NVARCHAR column. If it works more likely you have a problem after the value is sent to the db (perhaps in this string spliting function ?)
Mar 16, 2015 01:04 AM|Edwin Guru Singh|LINK
this data is coming from front end and I can't set such string directly in my stored procedure. I have to accept it to my nvarchar variable in SP. And that is where I'm stuck. as string will be without 'N', so it will return ????? to me.
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
2.Then you could set the encoding to UTF-8 in the
globalization element of your web.config
requestEncoding="utf-8" responseEncoding="utf-8" />
3.You don't need to prefix N for parameters. This is only required for literals. Just make sure your stored procedure or ADO.NET parameters are of Unicode type should be
4. For split your string using delimiter ";" .then you can use the below user-defined function :
-----------USER DEFINED FUNCTION-------------------------
CREATE FUNCTION [dbo].[fnSplitString]
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
declare @String nvarchar(50) =N'सारांश;abc;pqr'
select * from [dbo].[fnSplitString] (@String,';')
Mar 16, 2015 02:26 AM|rameezshaikh9|LINK
Thanks for your replies!!! Thanks Edwin Patrice :)
You made me to understand that N is only applied to literals. If a variable is nvarchar from beginning, you don't need to do any formatting.
As I said, I was accepting string in string datatype from front end. I was passing value in AddWithValue format. So, I created SqlParameter and put his DbType as NVarChar and it worked.
Dim param As SqlParameter = New SqlParameter("Que_Language_Text", SqlDbType.NVarChar, 4000)
param.Value = Question_Text
Here, Question_Text is string.