0

I have a table that has a column firstName, of type NVARCHAR. I'm writing a query to search by partial first name. My query looks like this:

$params[':firstName'] = "%วุฒิหิรัญทิ%";
$query = "SELECT * FROM users WHERE firstName LIKE :firstName";
$matches = $this-db->fetchAll($query, $params);

This doesn't work, and I've read that in MSSQL I need to use N to search Unicode characters. How do I accomplish this in a prepared statement? I tried replacing :firstName with N:firstName and it did not work.

EDIT: This solution works for characters in the Latin 1 character set. It does not work for characters outside that set. For special characters, I can run a query directly such as SELECT * FROM users WHERE firstName LIKE N'%วุฒิหิรัญท%' and it works fine, but I can't get it to work with prepared statements.

charmeleon
  • 2,693
  • 1
  • 20
  • 35
  • I'm not familiar with how the variable :firstname will be parsed in the query, but shouldn't you surround it with " ' "? Like this: `... LIKE ':firstname'` – tobypls Apr 27 '16 at 13:14
  • No, prepared statements generally avoid the usage of quotes – charmeleon Apr 27 '16 at 13:16
  • Ok and are you sure that you're using wildcard searches right for prepared statements? Have a look at http://stackoverflow.com/questions/1352002/using-wildcards-in-prepared-statement-mysqli – tobypls Apr 27 '16 at 13:19
  • The way I'm using them is working for characters in the latin1 character set. It does not work for UTF-8 characters though (in this case Thai) – charmeleon Apr 27 '16 at 13:27
  • Ultimately, the original query was working correctly. There seems to be an encoding issue and all unicode strings are being parsed as varbinary. I wrote a workaround since I still haven't found the source of the encoding issue. Since we'll be migrating DB engine before the end of the year I won't be pursuing the actual source any longer – charmeleon May 25 '16 at 12:39

1 Answers1

-1

You're not using SQL wildcards correctly;

SELECT * FROM users WHERE firstName LIKE '%' + :firstName + '%'

The % symbols are SQL's wildcard operator, the above should work for you.

Rich Benner
  • 7,873
  • 9
  • 33
  • 39
  • I see. This didn't work though, I got an error "The data types varchar and varbinary are incompatible in the add operator." I'm thinking that `'%'` is the varchar and the parameter is being treated as varbinary – charmeleon Apr 27 '16 at 12:48
  • You could remove the '%' from your parameter. Those are some very unusual characters it's using too, could you see if it would work with more common characters? – Rich Benner Apr 27 '16 at 12:49
  • Well, it works for characters in the latin1 character set, just like the original post did. It doesn't work for Thai/Cyrillic characters – charmeleon Apr 27 '16 at 13:28