0

i am trying to retrieve data from database using Unicode character ₹ with following query

select AnnualSalary,* from UserDetails where AnnualSalary like N'₹%'

I don't understand why this query is not working as normal like query, is there any other way to do this ? please suggest if there is another way

Wenfang Du
  • 8,804
  • 9
  • 59
  • 90
Dhaval Pankhaniya
  • 1,996
  • 1
  • 15
  • 26
  • 1
    `LIKE N'₹%' `... ? – BJones Apr 09 '18 at 13:42
  • [`N'₹%'`](https://stackoverflow.com/q/10025032/4137916). – Jeroen Mostert Apr 09 '18 at 13:42
  • yes i have tried with "like '₹%' " – Dhaval Pankhaniya Apr 09 '18 at 13:44
  • Which Sql Server version your using? – Nisha Salim Apr 09 '18 at 13:46
  • You're still missing an important part. LIKE **N**'₹%' – Aaron Bertrand Apr 09 '18 at 13:50
  • @NishaSalim am using sql server 2012 – Dhaval Pankhaniya Apr 09 '18 at 13:52
  • What is the type of the column `AnnualSalary`? Do you know it actually contains `₹` as returned by `SELECT *` or is that presentation markup? The name suggests it should contain `MONEY` or `DECIMAL` values, while your search makes sense only for `N(VAR)CHAR` columns. – Jeroen Mostert Apr 09 '18 at 13:55
  • @JeroenMostert type of AnnualSalary is nvarchar(max) and some of its rows contains Unicode character ₹. we are going to convert it to decimal that's why i need to filter those records – Dhaval Pankhaniya Apr 09 '18 at 13:58
  • What does `LIKE N'%₹%'` give? Your current query searches only values that start with the character, and even if existing values appear to do so, there may be invisible characters in front of it. To see exactly what's in a column, use `CONVERT(VARBINARY(MAX), AnnualSalary)` and puzzle over the contents as codepoints. The character you search for is `U+20B9 INDIAN RUPEE SIGN`, which would be encoded as `B920` in SQL Server. Do your binary strings all start with that? – Jeroen Mostert Apr 09 '18 at 14:00

5 Answers5

2

Maybe something like the following will help:

DECLARE @Tab TABLE (ID INT, Salary NVARCHAR(MAX))
INSERT @Tab VALUES (1, N'₹10,000'),(2,N'20,000'),(3,N'30,000'),(4,N'₹50,000')

SELECT ID, CAST(Salary AS nvarchar) Salary
FROM @Tab
WHERE UNICODE(Salary) = UNICODE(N'₹')
BJones
  • 2,450
  • 2
  • 17
  • 25
  • 1
    [Don't specify `NVARCHAR` without a length](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length). It's extra confusing in this case, since `UNICODE` only operates on the first character anyway, but without a length you get an `NVARCHAR(30)`. The `CAST` is unnecessary, but if you were intent on using it, use `NVARCHAR(1)`. – Jeroen Mostert Apr 09 '18 at 14:32
1

hey guys BJones ans is working fine for me and i also found another way and posting here more information 

select AnnualSalary,* from UserDetails where CHARINDEX(N'₹', AnnualSalary) > 0

this also gives me the same result as BJones ans give

Dhaval Pankhaniya
  • 1,996
  • 1
  • 15
  • 26
  • 1
    Interesting. Apparently the collation considers `₹` a functional blank. This may have something to do with the rupee sign being a relatively recent addition, and the default collation being out of date. If you force the collation to a binary one, you'll get consistent results (`WHERE AnnualSalary LIKE N'%₹%' COLLATE Latin1_General_BIN2`). – Jeroen Mostert Apr 09 '18 at 14:38
0

When you hard-code literals in unicode, you should add the N letter just before the start of the string.

This is non-unicode

'Your string'

This is unicode

N'Your string'

So your like should be

LIKE N'₹%'
EzLo
  • 13,780
  • 10
  • 33
  • 38
0
select AnnualSalary,* from UserDetails where AnnualSalary  like LIKE UNICODE(N'₹%')

Please try this.Its working in SQL Server 2017

Nisha Salim
  • 687
  • 5
  • 13
-3

you can try below one it will give you correct answer.

select AnnualSalary,* from UserDetails where AnnualSalary like '\U+20B9%';

Lokanath Reddy
  • 5
  • 1
  • 1
    SQL Server has no support for Unicode escape sequences whatsoever. This will search for the literal string `\U+20B9`. If you had no means of typing `₹`, the way to do it would be `NCHAR(8377) + '%'`, but that does not appear to be the issue here. – Jeroen Mostert Apr 09 '18 at 13:51
  • @Lokanath_Reddy sorry but your answer does't work for me – Dhaval Pankhaniya Apr 09 '18 at 13:53