October 30, 2008 at 12:04 pm
This query does not work:
SELECT * FROM INT_AUX_LISTING
WHERE FIRST_NM IN ('Jean')
COLLATE Latin1_General_CS_AS
The error is:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'COLLATE'.
Is this to-be-expected?
Similar queries which do work include
/* use = instead of IN */
SELECT * FROM INT_AUX_LISTING
WHERE FIRST_NM = 'Jean'
COLLATE Latin1_General_CS_AS
/* don't collate */
SELECT * FROM INT_AUX_LISTING
WHERE FIRST_NM IN ('Jean')
October 30, 2008 at 12:51 pm
Interesting, but you can get by with the JOIN
Tested on SQL 2008 AdventureWorks2008, should work on 2005 as well
USE AdventureWorks2008
SELECT * FROM Person.Person
WHERE FirstName IN ('Jean')
COLLATE Latin1_General_CS_AS -- Incorrect syntax near the keyword 'COLLATE'.
SELECT * FROM Person.Person P
INNER JOIN (
SELECT FirstName = 'Jean'
) T ON P.FirstName = T.FirstName
COLLATE Latin1_General_CS_AS -- works
October 30, 2008 at 2:12 pm
jeanlibera (10/30/2008)
This query does not work:SELECT * FROM INT_AUX_LISTING
WHERE FIRST_NM IN ('Jean')
COLLATE Latin1_General_CS_AS
The error is:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'COLLATE'.
Yes, because COLLATE can only be applied to charcter-based columns and character expressions.
" ... IN(...)" is neither, it is a logical expression.
Note that if you move the IN function's last parenthesis to include the COLLATE, it works:
SELECT * FROM INT_AUX_LISTING
WHERE FIRST_NM IN ('Jean'
COLLATE Latin1_General_CS_AS)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 30, 2008 at 2:15 pm
Jerry Hung (10/30/2008)
Interesting, but you can get by with the JOINSELECT * FROM Person.Person P
INNER JOIN (
SELECT FirstName = 'Jean'
) T ON P.FirstName = T.FirstName
COLLATE Latin1_General_CS_AS -- works
[/code]
This does not apply the COLLATE to the JOIN. Rather it is applying it to the character column (T.FirstName) at the end of the ON clause. Note that if you add parenthesis around the ON clause conditions, it will once again fail because it is now being applied to the logical expression, which is invalid:SELECT * FROM Person.Person P
INNER JOIN (
SELECT FirstName = 'Jean'
) T ON (P.FirstName = T.FirstName)
COLLATE Latin1_General_CS_AS -- fails
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 31, 2008 at 9:07 am
great tips, thanks rbarryyoung
October 31, 2008 at 9:12 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 6, 2012 at 6:48 pm
Jean, I guess the first question is why do you want to force case and accent sensitivity? It will ignore the index on the field and hurt performance.
April 9, 2012 at 6:49 am
Henry Treftz (4/6/2012)
Jean, I guess the first question is why do you want to force case and accent sensitivity? It will ignore the index on the field and hurt performance.
You do realize that this thread is almost FOUR YEARS OLD. On top of that, the OP hasn't logged in in almost two years.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 9, 2012 at 6:53 am
Yep I realized both...
I used to work on the product that the poster mentioned and was partially giving them a hard time about trying to do it....
🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply