3

From my experience it seems that select where like on data from my database doesnt work on fields in nchar format but does in nvarchar format. Is this true?

If so why and is changing the data type to nvarchar the only work around?

I have noticed that nchar is of ANSIString type and nvarchar is just String but that seems odd that one works and the other doesnt when they're both unicode.

Crouch
  • 846
  • 3
  • 17
  • 32
  • 2
    When you are searching for `nchar` are you accounting for the spaces at the end of the string? For example for the word "Hello" using `nvarchar(6)` would have it be `"Hello"` while `nchar(6)` would have it be `"Hello "` – Scott Chamberlain Oct 28 '13 at 15:09
  • 1
    Along these lines, you can try putting '%' at the end of your comparison string in the like clause which will also account for unlimited spaces (along with anything else after your comparison string of course) – nycdan Oct 28 '13 at 15:10
  • 4
    To the idiot who voted to close this, go away, back to the depths of your basement. To the OP, is cast(column as varchar) that unwieldy? I've never tried using like with nchar, but it's most likely that you're not accounting for the fact that nchar fields are space padded, as Scott noted. – Chris Oct 28 '13 at 15:11
  • @ScottChamberlain no I arent accounting for spaces. Well I never thought I was apparently I am – Crouch Oct 28 '13 at 15:11
  • @nycdan I am yes like so `'%' + @field + '%'` – Crouch Oct 28 '13 at 15:13
  • 2
    Well then you are accounting for padding at the end so that's not your problem. Why not post your exact code and we'll see if that helps. – nycdan Oct 28 '13 at 15:14
  • @Chris cheers for that yeah neither have I this was the first time i'd witnessed it, it's not a datatype i've used often in honest – Crouch Oct 28 '13 at 15:14
  • possible duplicate of [SQL Server char and nchar columns search differently](http://stackoverflow.com/questions/16171177/sql-server-char-and-nchar-columns-search-differently) – Martin Smith Oct 28 '13 at 16:33
  • similar @MartinSmith but the answers I received on here were not on there, some similar answers though. – Crouch Oct 29 '13 at 08:40

1 Answers1

1

Turns out you were all pretty much on the money, it is a padding related issue an nchar or char has one space to right of it as padding which means I'd have to do something like this

SELECT * FROM foo WHERE bar LIKE '%bb %'

Instead of this

SELECT * FROM foo WHERE bar LIKE '%' + @field + '%'

This works the other way round to the latter query will select all fields that are nvarchar etc. but wont select fields that are nchar or char.

(EDIT)

SELECT * FROM foo WHERE bar LIKE '%bb%'

This solution will work on both sets of data types I have mentioned. No space needed after the bb

Crouch
  • 846
  • 3
  • 17
  • 32