0

Here is the deal.

I have 2 databases. One is older and has expanded data. The other is newer and has less relevant data. They both share the same products just one has more data.

I've begun a project where I want to expand the newer database to include some of the missing data that exists in the older one. Problem is the IDs don't match up between the databases. So I'm having to search by names. Which may or may not be the same case. The queries in visual studio are DEFINITELY case sensitive. I tested this and I am sure.

So my first thought was to do a like search with a lower function. Like this:

WHERE lower([Name1]) LIKE lower('%Name2%')

but when I went to run it it gave me an error. And visual studio automatically tried to change the syntax of the statement to this:

WHERE 'lower'([Name1]) LIKE 'lower'('%Name2%')

I could have sworn lower() was the right syntax. And I can't find anywhere on google saying any alternatives or why visual studio wouldn't like it. In fact I just tried a similar line in SQL Management Studio and it worked. Why is it not working in Visual Studio?

Matías Fidemraizer
  • 63,804
  • 18
  • 124
  • 206
Bill
  • 95
  • 2
  • 10
  • 3
    What **exactly** are you doing in Visual Studio that isn't working? – Neil Barnwell Sep 01 '11 at 20:14
  • You dont need to use Lower to do a string comparison. SQL Server is case-insensitive in its string comparisons by default. – Neil N Sep 01 '11 at 20:16
  • "gave me an error." an the error was.... – Conrad Frix Sep 01 '11 at 20:17
  • I thought I described what I was doing pretty well. I'm working in visual studio's server explorer in a new query window. What were you unclear about? – Bill Sep 01 '11 at 20:17
  • 1
    @Neil: incorrect, sql string comparisons are subject to the case sensitivity of the collation of the data involve din the comparison. – Remus Rusanu Sep 01 '11 at 20:19
  • Neil N As I already stated I already tested this. It is definitely case sensitive. Like '%NAME%' showed results. Like '%Name%' did not. I know beyond a doubt it is case sensitive. – Bill Sep 01 '11 at 20:19
  • Why not start with the exact error you received when you tried to run the query? – Adam Maras Sep 01 '11 at 20:20
  • Conrad the error is "Undefined function ''LOWER'' in expression" (notice the double single quotes. I typed it without single quotes. Visual studio tried to make the function a char type for some reason when I ran it.) – Bill Sep 01 '11 at 20:21
  • Can you post a screen shot somewhere? It's very hard to envision why Visual Studio would treat the same query syntactically different just because it's running against a different database. Is one of the databases not SQL Server? – Aaron Bertrand Sep 01 '11 at 20:22
  • Well that's possible. I'm pretty new to SQL so excuse my amateure language. But I was under the impression that anything running in Visual Studio used SQL Server... BUT if it's attached to the database then it's no wonder I'm having issues. This Database was built god knows when. It's meant for a DOS app that was built sometime in the 80s. – Bill Sep 01 '11 at 20:32
  • I think it's an ODBC database. Does that sound right? – Bill Sep 01 '11 at 20:36
  • Is that it? Is it the age the the database? How can I correct this? – Bill Sep 01 '11 at 21:13

3 Answers3

1

Use COLLATE to specify case sensitivity. Simply force a case insensitive collation, if needed. It may not be needed, depending on the existing collation of the data. Eg.:

SELECT ... FORM ...
WHERE Name1 COLLATE SQL_Latin1_General_CI LIKE '...';
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • COLLATE doesn't work either... lol I'm starting to think it's the age of the database I'm working in. It's from the 80s. – Bill Sep 01 '11 at 20:33
1

First off VS does not execute the query, the collation on the column that you are querying will determine if SQL treats it as case sensitive or not. Also because you are using LIKE in your comparison what you actually want is something more like:

WHERE lower([Name1]) LIKE '%' + lower([Name2]) '%'
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
HasaniH
  • 8,232
  • 6
  • 41
  • 59
0

Use WHERE lower([Name1]) LIKE '%name2%' Since that value is constant (or input?) you don't need to convert it to lower here. You can do that beforehand. Also, not sure about the [ ... ]. They shouldn't be needed either.

But I think the best option would be to tell the database that you want to compare case insensitive, like so:

Community
  • 1
  • 1
GolezTrol
  • 114,394
  • 18
  • 182
  • 210