Case sensitive compare

Our database server is set up for case insensitive compares. In other
words, both
select 1 from tbl where OrderID = 'QQQ'
select 1 from tbl where OrderID = 'qqq'
will return a row. Is there a way to force a case sensitive comparison
in just this one query without messing with the server settings?
ThanksFrank,
SQL 2000? Check out the COLLATE clause in the SQL Server BOL for your
expression.
HTH
Jerry
"Frank Rizzo" <none@.none.com> wrote in message
news:uOtBpOHxFHA.2000@.TK2MSFTNGP10.phx.gbl...
> Our database server is set up for case insensitive compares. In other
> words, both
> select 1 from tbl where OrderID = 'QQQ'
> select 1 from tbl where OrderID = 'qqq'
> will return a row. Is there a way to force a case sensitive comparison in
> just this one query without messing with the server settings?
> Thanks|||Frank
select 1 from tbl where OrderID = 'QQQ' COLLATE Latin1_General_BIN
"Frank Rizzo" <none@.none.com> wrote in message
news:uOtBpOHxFHA.2000@.TK2MSFTNGP10.phx.gbl...
> Our database server is set up for case insensitive compares. In other
> words, both
> select 1 from tbl where OrderID = 'QQQ'
> select 1 from tbl where OrderID = 'qqq'
> will return a row. Is there a way to force a case sensitive comparison in
> just this one query without messing with the server settings?
> Thanks|||This might be a solution :
SELECT *
FROM
aa A
INNER JOIN
aa B
ON
A.aa = B.aa
AND
CAST(A.aa AS varbinary) = CAST(B.aa AS varbinary)
The first compare in the ON clause is to make an efficient match of the two
fields to match. The second compare in the ON clause is to make sure that
the compare becomes case sensitive.
(The second on it's own is enough in a lot of queries, but the first might
speed up the process because the first compare can use indexes, the second
can not).
The following gives the same result. (This was given in one of the other
mails). (Maybe in this case the first compare can be ommitted, because
it might be possible that the second compare can use indexes.)
This method may be prefereble to the method suggested above.
SELECT *
FROM
aa A
INNER JOIN
aa B
ON
A.aa = B.aa
AND
( A.aa = B.aa COLLATE Latin1_General_BIN)
good luck,
ben brugman
"Frank Rizzo" <none@.none.com> wrote in message
news:uOtBpOHxFHA.2000@.TK2MSFTNGP10.phx.gbl...
> Our database server is set up for case insensitive compares. In other
> words, both
> select 1 from tbl where OrderID = 'QQQ'
> select 1 from tbl where OrderID = 'qqq'
> will return a row. Is there a way to force a case sensitive comparison
> in just this one query without messing with the server settings?
> Thanks|||Additional,
In both examples, the first compare can speed
up the query. The Cast and Collate constructions
both do not use available indexes. Ben
"ben brugman" <ben@.niethier.nl> wrote in message
news:eptJkZOxFHA.3300@.TK2MSFTNGP09.phx.gbl...
> This might be a solution :
> SELECT *
> FROM
> aa A
> INNER JOIN
> aa B
> ON
> A.aa = B.aa
> AND
> CAST(A.aa AS varbinary) = CAST(B.aa AS varbinary)
> The first compare in the ON clause is to make an efficient match of the
two
> fields to match. The second compare in the ON clause is to make sure that
> the compare becomes case sensitive.
> (The second on it's own is enough in a lot of queries, but the first might
> speed up the process because the first compare can use indexes, the second
> can not).
> The following gives the same result. (This was given in one of the other
> mails). (Maybe in this case the first compare can be ommitted, because
> it might be possible that the second compare can use indexes.)
> This method may be prefereble to the method suggested above.
> SELECT *
> FROM
> aa A
> INNER JOIN
> aa B
> ON
> A.aa = B.aa
> AND
> ( A.aa = B.aa COLLATE Latin1_General_BIN)
>
> good luck,
> ben brugman
>
> "Frank Rizzo" <none@.none.com> wrote in message
> news:uOtBpOHxFHA.2000@.TK2MSFTNGP10.phx.gbl...
> > Our database server is set up for case insensitive compares. In other
> > words, both
> > select 1 from tbl where OrderID = 'QQQ'
> > select 1 from tbl where OrderID = 'qqq'
> >
> > will return a row. Is there a way to force a case sensitive comparison
> > in just this one query without messing with the server settings?
> >
> > Thanks
>

Capturing SQL noise word exception...

Hi All,

I have a requirement where I need to find the list of noise words from a set of words in a SP.

PS: Too common words are said to be noise words and SQL maintains list of noise words on its own.

Say I have a sentence like "I am a software engineer. Here I need to get the list of noise words (I, am, a).I have written a logic where I will split the sentence into words and process word by word. I will first take one word and I have a select statement which will throw SQL noise word error exception if it is noise word.

Logic is
1. Take a word from the sentence.
2. Write a select statement like "select * from job where contains (jobdescription,' extracted word')"
3. If the word is noise word then SQL will throw a noise word exception.
4. I try to capture this error and based on that I have some logic.
5. If noise error thrown (I am using @.@.error)
do this;
do this;
else
do this;
do this;
6. Now my problem is, when the SQL throws noise exception, the execution of the SP stops immediately and the rest of the logic is not executed.
7. But some how I need to capture the exception and continue with the program flow.

I have different logic where I can achieve my requirements. (Instead of capturing SQL exception, maintain the noise words in a table and check with the table). but my question is there any way where I can capture the SQL exception and continue the program flow?

Please reply to my mail id.

TIA,
Varada.Can we do this 1 problem at a time?

Are you looking for a word in a string?

Look up CHARINDEX