0

I have a search form (with method GET) with only one text field named “search_field”. When a user submits the form, the typed by the user characters are posted to the URL. For example if the user type "blablabla" the generated URL will be something like that:

results.asp?search_field=blablabla

In my MSSQL 2012 database I have a table named “Products” with a column named “kodikos” in it.

I want to display all the records from the column “kodikos” containing the typed characters. My SQL select statement if the following:

"SELECT * FROM dbo.Products WHERE dbo.Products.kodikos LIKE '%' + ? + '%' "

(the question mark is the “search_field” that contains the typed by the user characters.

All the above works perfect and I am getting the correct results. The problem that I am facing is with the Greek characters. For example when the user type “fff” my codes works perfect and finds all the records containing the characters “fff”. Also works perfect with numbers too. But if the user type in Greek characters “φφφ” I am not getting any results. And there are a lot of records with “φφφ”. The problem is that the Greek characters are not recognized at all.

For your information:

  1. In my local PC with the same SQL version the Greek characters are recognized correctly with my code, because my regional settings are set in Greek. But the same code in the hosting server in US does not recognize them.

  2. All of my pages have UTF-8 encoding.

Can someone have any idea to solve this issue???

user692942
  • 16,398
  • 7
  • 76
  • 175
Freddakos
  • 97
  • 1
  • 11
  • 2
    This is essential reading for anyone using non Western European character sets with Classic ASP. https://www.hanselman.com/blog/InternationalizationAndClassicASP.aspx . NB, Are you using a parameterised query? If you aren't then you're vulnerable to a sql injection attack. – John Mar 27 '17 at 15:03
  • You need to show some code before you will get any meaningful answers, how are the querying SQL Server in your Classic ASP code? – user692942 Mar 27 '17 at 19:57
  • Just saying *"All of my pages have UTF-8 encoding"* isn't enough, that doesn't mean you won't have an encoding mismatch. – user692942 Mar 27 '17 at 20:01
  • Related [A: convert utf-8 to iso-8859-1 in classic asp](http://stackoverflow.com/a/17680939/692942) *(read the answer, question title is misleading)*. – user692942 Mar 27 '17 at 20:08
  • Related [A: Convert UTF-8 String Classic ASP to SQL Database](http://stackoverflow.com/a/21914278/692942) *(again the question title is misleadingly)*. – user692942 Mar 27 '17 at 20:11

2 Answers2

1

SQL Server knows two encodings natively:

  • 2-byte-unicode (in most cases NVARCHAR)
  • extended ASCII in connection with a collation (in most cases VARCHAR)

I assume, that the language you are calling this from is using 2-byte-unicode for normal strings. This is pretty usual today...

I assume, that your column Products.kodikos is of type NVARCHAR (2-byte-unicode). In this case it should help to force your search string to be 2-byte-unicode too. Try

LIKE N'%' + CAST(? AS NVARCHAR(MAX)) + N'%'

If your column is not 2-byte encoded it might help to use COLLATE to force your search string to know your special characters.

If you pass this string into a SQL-Server routine as-is, you should make sure, that the accepting parameter is 2-byte-unicode too.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • I tried your code but unfortunately don’t work. FYI the data type is nvarchar(100) – Freddakos Mar 27 '17 at 14:50
  • @user2986570 What does *don't work* mean? no result? error? However: As the column is `nvarchar(100)` you must make sure, that your command is using `N'literal'` syntax, that the string you pass over is *2-byte-unicode* actually and all variables/functions you might use/call in between should be `NVARCHAR` too... – Shnugo Mar 27 '17 at 15:22
  • Not an error. But keeps not returning results. And I know that are a lot records containing the typed characters. Just keeps to not recognize Greeks.. – Freddakos Mar 27 '17 at 15:32
  • @user2986570 Did you try to use the Profiler to monitor the queries, which reach the server? Check, what is going in and what is coming out. You should - for many reasons! - **prefer typed parameters!** – Shnugo Mar 27 '17 at 15:41
  • Can you please provide me with the correct syntax to try the COLLATE method? The Greek collate is Greek_CI_AS – Freddakos Mar 27 '17 at 19:25
  • None of these answers are useful, the issue will be Classic ASP not SQL, next time don't jump all over an obvious dup question. – user692942 Mar 27 '17 at 19:55
0

You have to make sure your search string is two byte encoded using the N'' notation...

For instance, the following query uses a string that is two byte encoded:

SELECT * FROM dbo.Products WHERE dbo.Products.kodikos LIKE N'%φφφ%'

But this query uses a string that is not two byte encoded (you won't get any results):

SELECT * FROM dbo.Products WHERE dbo.Products.kodikos LIKE '%φφφ%'
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
  • The issue will be Classic ASP not SQL, this is what happens when you jump on obvious dup questions that have been asked many times. – user692942 Mar 27 '17 at 19:56
  • @Lankymart, For me this is far away from an *obvious dup question* due to my lack of `asp classic`. Such issues are quite common in T-SQL too... Thx for your input! – Shnugo Mar 28 '17 at 07:50
  • The issue is actually two parts. First, in Classic ASP you have to send a UTF-8 string to the query (that's the duplicate part you talked about). Second, in SQL you have to build the query in such a way that is accepts a UTF-8 string (this is how my answer differs from the dup question). – bastos.sergio Mar 28 '17 at 09:50
  • @bastos.sergio for starters they should be using parameterised queries or even a stored procedure then it's just a case of defining `adVarWChar` parameters and `N'` doesn't need to be a concern. – user692942 Mar 29 '17 at 21:25