With NVARCHAR data type, I store my local language text in a column. I face a problem how to query that value from the database.
I wrote SQL like this
select DivisionName
from t_Et_Divisions
where DivisionName = 'ዜናገብርኤልስ'
select unicode (DivisionName)
from t_Et_Divisions
where DivisionName = 'ዜናገብርኤልስ'
The above didn't work. Does anyone have any ideas how to fix it?
Thanks!
You need to prefix your Unicode string literals with a N:
select DivisionName
from t_Et_Divisions
where DivisionName = N'ዜናገብርኤልስ'
This N prefix tells SQL Server to treat this string literal as a Unicode string and not convert it to a non-Unicode string (as it will if you omit the N prefix).
Update:
I still fail to understand what is not working according to you....
I tried setting up a table with an NVARCHAR column, and if I select, I get back that one, exact row match - as expected:
DECLARE @test TABLE (DivisionName NVARCHAR(100))
INSERT INTO @test (DivisionName)
VALUES (N'ዜናገብርኤልስ'), (N'ዜናገብርኤልስ,ኔትዎርክ,ከስተመር ስርቪስ'), (N'ኔትዎርክ,ከስተመር ስርቪስ')
SELECT *
FROM @test
WHERE DivisionName = N'ዜናገብርኤልስ'
This returns exactly one row - what else are you seeing, or what else are you expecting??
Update #2:
Ah - I see - the columns contains multiple, comma-separated values - which is a horrible design mistake to begin with..... (violates first normal form of database design - don't do it!!)
And then you want to select all rows that contain that search term - but only display the search term itself, not the whole DivisionName column? Seems rather pointless..... try this:
select N'ዜናገብርኤልስ'
from t_Et_Divisions
where DivisionName LIKE N'%ዜናገብርኤልስ%'
The LIKE searches for rows that contain that value, and since you already know what you want to display, just put that value into the SELECT list ....
DivisionName? Then of course all three of them are returned - that's how SQL SELECT works!
ዜናገብርኤልስ)... you should use theSqlParameterobject. That will implicitly solve all the unicode problems plus many other problems.