uninstall SQL: insensitive

Showing posts with label insensitive. Show all posts

Tuesday, March 27, 2012

Case sensitivity problems...

I know that SQL SERVER is by default case insensitive but all my searches are case sensitive.....I also tried the ALTER DATABASE COLLATE ..... Still no luck...Please help!!!!
thanks in advance,
jPlease clarify: do you mean that SQL server is using case-sensitive searches despite the default, or do you mean that you WANT all your searches to be case-sensitive?

blindman

Sunday, March 25, 2012

case sensitivity

Is SQL server defaults to case insensitive? I am sure there are ways to
install case sensitive SQL server instance but coming from Sybase
(which is always case sensitive) case insensitivity is something new to
me (it requires coding change etc).
Besides, is there any option I can set to turn on case sensitivity or I
am stuck with it? (I can not reinstall SQL server).
Thanks.Case sensitivity is defined by Collation. Read a bit about collations and
youll see what are your options.

<othellomy@.yahoo.comwrote in message
news:1163396241.216022.146940@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

Case sensitive/insensitive

Hi, I configure the SQL Server as case-insensitive. Can I configure a particular table's column to be case-sensitive
Thank you.During the instalation you can choose the default collation for all
databases, for a particular database you can define this durin the database
creation process.
Look at bol for COLLATE clause.
HTH
Wandenkolk T. Neto
MCSE, MCDBA, MCP
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||To add to Wandenkolk's response, in SQL Server 2000 you can specify
COLLATE at the column level to control case sensitivity.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Keith" <anonymous@.discussions.microsoft.com> wrote in message
news:10435BC6-7691-4655-8D52-CAD92F45FBD0@.microsoft.com...
> Hi, I configure the SQL Server as case-insensitive. Can I configure a
particular table's column to be case-sensitive?
> Thank you.|||I was also wondering about case senitivity on columns only.
Under EM on each table I found where I can change the collate... but
there is about 50 different ones listed. Where can I find out about
the differences. Mine is currently set to
"SQL_Latin1_General_CP1_CI_AI"
Whatever that is...
Al.
On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:
>To add to Wandenkolk's response, in SQL Server 2000 you can specify
>COLLATE at the column level to control case sensitivity.|||You can get a list of available collations with fn_helpcollations:
SELECT * FROM ::fn_helpcollations()
This is described in the COLLATE topic in the Books Online
<tsqlref.chm::/ts_ca-co_5z55.htm>.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Harag" <harag@.REMOVETHESECAPITALSsofthome.net> wrote in message
news:7m7hrv4toku4433mpmfqf343ultn44ntuu@.4ax.com...
> I was also wondering about case senitivity on columns only.
> Under EM on each table I found where I can change the collate... but
> there is about 50 different ones listed. Where can I find out about
> the differences. Mine is currently set to
> "SQL_Latin1_General_CP1_CI_AI"
> Whatever that is...
> Al.
> On Sun, 16 Nov 2003 21:47:25 -0600, "Dan Guzman"
> <danguzman@.nospam-earthlink.net> wrote:
> >To add to Wandenkolk's response, in SQL Server 2000 you can specify
> >COLLATE at the column level to control case sensitivity.
>sql

Case Sensitive vs Insensitive

After all the pain I've been going through with code pages and collation, I was asked how, when sql server does it's joins and predicate searches, how does it actual (internals now) know the an "A" = "a" in an insensitive search?

I didn't have the answer.

Damn, Now I really have to pick up Kelans book.I've seen tricks suchs as uppercasing or lowercasing both prior the compare. I think formatting routines such as these can be very quick because they only have lookup's to do.|||Well, the questions stems from an insensetive server

You don't need to use CASE functions in this case

"A" = "a"
"A" = "A"
"a" = "A", and
"a" = "a"

Internally each has it's on ASCII represenation.

How does an insesitive ("the big jerk") server resolve this?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?|||Perhaps it "ands" 32 to both characters if either are in the ASCII range 65-90? Probably a number of ways of doing it. Is this one of those questions where an IT Director thinks he has the better of you, because he found a question you can not answer?

No, they don't know, and because DB2 OS/390 is case sensitive, he didn't understand...and because I've got sql boxes built six ways to sunday, it's been bugging me...and that's how it arose...

But now it's bugging me as well...|||helluva question - 1st i'd smack the guy who asked.

it's just like asking how does SQL server know 1 != 2?
not a technically valid question, IMHO, but thats way the cookie crumbles sometimes, i reckon.

my understanding is that collation is a 3 legged stool
the sort order + the code page + the character set or "dictionary".

if the sort order is binary, my personal favorite,
its pretty simple: the bit pattern mapped to 'a' is not the same as 'A'. what ends up writing to disk is simply as different between 'a' and 'A' as it is between '1' and '2'. it knows 'a' is not 'A' because they are completely differnt patterns stored on the disk.

i dont think i've ever totally understood how non-binary case-sensitive sort orders know the difference - but i do know it involves 'interrogating' the character set dictionary at a very very low level to get the info it needs - which is why it is a noticably slower way to store/join/compare your data.

both binary and dictionary sorts 'technically' use bit patterns, but sometimes a particular character set stored in non-binary can have funky characters and stuff that make it not "always" work.

in either scenario, depending on the level of the person asking the question - i'd think the bit pattern explanation is close enough for gov't work, after the back-hand, of course.|||I think ascii comparisons are from the old days. If case-insensitive is set, I would think it'll look at the codepage and have the codepage decide which character is its uppercase or lowercase equivalent.|||helluva question - 1st i'd smack the guy who asked.

Now do you seriously think I'd entertain this question from someone who didn't SIGN my check?

Anyway, it'a valid question, and from what ya'll (no I'm not from the south) have mentioned, it makes a lot of sense, and was thinking in those terms...

Still gotta google some details...

Thanks|||LOL - i know what you mean.
Gotta make that mortgage payment.|||The problem is that the actual process isn't that simple. There is bitmap that shows which charcters are mapped, and a hash of arrays that allows each unicode character set to "remap" characters as needed. Each array contains the hash signature, a value for comparison, and a value for sorting. Most characters don't have lookup entries in the hash, so they use the actual binary value.

Aren't you glad that you asked?

-PatP|||hey pat -
does the 'array' you refer to only store the unicode, and special non-unicode characters for languages that use them (tilde, etc)? or all characters?

sounds as though the premise that the code page has all the info in it and SQL services communicate with its contents for sorting and comparison rules is the basic idea for dictionary sorts - or it uses the binary value directly for binary sorts.

is that your take, at a high level?|||The hash is the actual lookup mechanism. The array that is returned from the hash contains values that are used for comparisons and sorting.

You can think of it something like this: When processing a character, it gets expanded to 16 bits if it wasn't already, then that 16 bit value is used as a lookup into the bitmask to see if this character gets special processing (most don't). If it needs processing, a hash lookup is done which returns the character itself, the value to use for comparison purposes, and a value to use for sorting purposes. If the character didn't need processing, it is copied into all three values in an empty array.

-PatP|||thanks!
'splains a little better why binary sorts are mo'better, IMO.
they need no translation and/or re-sorting.

of course, some users expect charater ordering to work like it did for them in 3rd grade, but, eh, NMFP. the database is faster, that IS my problem.

i'm starting to think i'm an evil dba...

:confused:|||Once you get past the surface...it's all about the internals and undersatanding how things work...

Thanks again Pat...

(He has a signed copy of Kelans book no doubt)|||quite true.
there's so much to learn i'll be busy the rest of my life.

might be useful for this thread and/or compiling a detailed answer for your bossman:

master..syscharsets is where SQL server goes to get the binary definitions for character sets and sort orders.

peace.|||(He has a signed copy of Kelans book no doubt)That I do! If you mean Kalen's Inside Microsoft SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=UM7Mwdc9jn&isbn=0735609985&itm=1).

-PatP

case sensitive to case insensitive

I've just restored a db (which relies on CASE INSENSITIVE) to another db
server which happens to have been set to CASE SENSITIVE.
Is it possible to change the settings of just 1 db within the overall sql
server instance, to allow it to continue as case insensitive.?
How can I get around this problem?
ThanksJack
ALTER DATABASE ..,statement allows you to change a collation , however
only on new created objects
Can you re-intsall the instance?
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
> I've just restored a db (which relies on CASE INSENSITIVE) to another db
> server which happens to have been set to CASE SENSITIVE.
> Is it possible to change the settings of just 1 db within the overall sql
> server instance, to allow it to continue as case insensitive.?
> How can I get around this problem?
> Thanks
>
>|||Hi
At the moment I can't reinstall the instance.
Are you saying with the ALTER DATABASE that I can retain the case
insensitive of the 1 db , within the Server which is set at CASE SENSITIVE ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> ALTER DATABASE ..,statement allows you to change a collation , however
> only on new created objects
> Can you re-intsall the instance?
>
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another db
>> server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall sql
>> server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>|||Jack
I said that only new cteated objects will have a new collation
(insensitive)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
> Hi
> At the moment I can't reinstall the instance.
> Are you saying with the ALTER DATABASE that I can retain the case
> insensitive of the 1 db , within the Server which is set at CASE SENSITIVE
> ?
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation , however
>> only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another db
>> server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>|||Do you know of any way apart from reinstalling the server , which would
allow the previously installed objects to retain their collation ?
For example, instead of doing a BACKUP/RESTORE , is it possible to create
the DB with the relevant collation and then transfer the objects over?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Jack
> I said that only new cteated objects will have a new collation
> (insensitive)
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>> Hi
>> At the moment I can't reinstall the instance.
>> Are you saying with the ALTER DATABASE that I can retain the case
>> insensitive of the 1 db , within the Server which is set at CASE
>> SENSITIVE ?
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation ,
>> however only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another
>> db server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>>
>|||Jack
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
It is not an easy task , because you need to rebuild indexes on the table
as well
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
Yes , create a database .. COLLATE ... (see in the bol for details)
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:RqednUq0eZUz3KvYRVnygA@.bt.com...
> Do you know of any way apart from reinstalling the server , which would
> allow the previously installed objects to retain their collation ?
> For example, instead of doing a BACKUP/RESTORE , is it possible to create
> the DB with the relevant collation and then transfer the objects over?
> Thanks
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OESvuLs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> I said that only new cteated objects will have a new collation
>> (insensitive)
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:PLCdneW5la6cpKvYnZ2dnUVZ8s2dnZ2d@.bt.com...
>> Hi
>> At the moment I can't reinstall the instance.
>> Are you saying with the ALTER DATABASE that I can retain the case
>> insensitive of the 1 db , within the Server which is set at CASE
>> SENSITIVE ?
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHxMzBs8GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Jack
>> ALTER DATABASE ..,statement allows you to change a collation ,
>> however only on new created objects
>> Can you re-intsall the instance?
>>
>>
>> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
>> news:f96dnRzj3NqRq6vYnZ2dnUVZ8tadnZ2d@.bt.com...
>> I've just restored a db (which relies on CASE INSENSITIVE) to another
>> db server which happens to have been set to CASE SENSITIVE.
>> Is it possible to change the settings of just 1 db within the overall
>> sql server instance, to allow it to continue as case insensitive.?
>> How can I get around this problem?
>> Thanks
>>
>>
>>
>>
>

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
>

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?
Thanks
Frank,
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...
>
sql

Case sensitive compare

Thursday, March 22, 2012

Case Insensitivity

I have a SQL 2000 database. I have a ASP.NET web app that I use to
search this database. I need to make my data case insensitive,
espcially my last name column. How do I change this?

Thanks,
BrianI was doing some further reading and I am hearing that you set case
sensitivity when you first install SQL by choosing an ANSI set and the
only way to change this is to re-install SQL. Is this correct? There
has to be another way around this...|||See "Specifying Collations" and "Collation Precedence" in Books
Online. You can change the collation at the database or column level
(see ALTER DATABASE and ALTER TABLE), or in your queries (see COLLATE).

Personally, I would modify the queries (or perhaps create a view)
rather than have one or two columns in a database in a different
collation from the rest.

Simon|||There is another way in SQL2000. Collation is determined at column
level so you can alter the case-sensitivity and other collation
properties at any time. For example:

ALTER TABLE YourTable
ALTER COLUMN last_name VARCHAR(50)
COLLATE Latin1_General_CI_AS

Read the Collations topics in Books Online to understand the collation
syntax and how this affects comparisons between columns of different
collation.

--
David Portas
SQL Server MVP
--|||I used your syntax and everything works like a charm except for one
thing, now when I do a search, such as "W" in the lastname field, it
pulls every records that contains a "W" in the last name, rather than
names that start with "W". How do you correct this? it needs to search
from left to right.

Thanks,
Brian|||What's the SQL statement you are using to SELECT? It sounds like
you're putting a wildcard in front of and behind the character you are
searching on, e.g.:

SELECT ColName
FROM Table
WHERE ColName LIKE '%W%'

when it sounds like you want the wildcard after

SELECT ColName
FROM Table
WHERE ColName LIKE 'W%'

Your collation settings should only affect the case sensity of the
database; not how your LIKE comparisons perform. Am I
misunderstanding?

Stu

case insensitive searching in sql server 2000 when its case sensitive

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?

thanks in advance.

You could use

SELECT A, B, C FROM TABLENAME WHERE UPPER(A) = UPPER(@.A)

Will have a look later

|||

Here you go:

http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html

This tutorial is for case sensitive. But you can easily make it case insensitive by change is CS to CI like this:

Change: COLLATE Latin1_General_CS_AS

To: COLLATE Latin1_General_CI_AS

|||

You can also use
SELECT A, B, C FROM TABLENAME WHERE A COLLATE Latin1_General_CI_AS = @.A COLLATE Latin1_General_CI_AS

The COLLATE forces a given collation.

Tuesday, March 20, 2012

Case Insensitive Lookups?

Is there an easy way to get the Lookup component to ignore case?No. Either use a Fuzzy Lookup or convert everything to lower/upper case in the queries using extra columns for the lookups.sql

case insensitive

Hi
How do I see if my MS-SQL Server is case insensitive
Br
HenningCheck out sp_helpdb in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
Hi
How do I see if my MS-SQL Server is case insensitive
Br
Henning|||> How do I see if my MS-SQL Server is case insensitive
One method:
IF 'A' = 'a'
PRINT 'case insensitive'
ELSE
PRINT 'case sensitive'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> Hi
> How do I see if my MS-SQL Server is case insensitive
> Br
> Henning
>|||Hi
Run sp_helpdb
in the result of sp_helpdb's status column if Collation says XX_CI_xx then
its case insensitive
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> Hi
> How do I see if my MS-SQL Server is case insensitive
> Br
> Henning
>|||Thank You, simple as that :)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
>> How do I see if my MS-SQL Server is case insensitive
> One method:
> IF 'A' = 'a'
> PRINT 'case insensitive'
> ELSE
> PRINT 'case sensitive'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> How do I see if my MS-SQL Server is case insensitive
>> Br
>> Henning
>|||My SQL is then case sensitive
How do I do-it case INsensitive? Is it simple?
One base I must install, must have it case insenitive
br
Henning
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
> Thank You, simple as that :)
>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
>> How do I see if my MS-SQL Server is case insensitive
>> One method:
>> IF 'A' = 'a'
>> PRINT 'case insensitive'
>> ELSE
>> PRINT 'case sensitive'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "H.Gjerde" <hg@.norspace.no> wrote in message
>> news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> How do I see if my MS-SQL Server is case insensitive
>> Br
>> Henning
>>
>|||"H.Gjerde" <hg@.norspace.no> wrote in message
news:ub1Np9LuHHA.484@.TK2MSFTNGP06.phx.gbl...
> My SQL is then case sensitive
> How do I do-it case INsensitive? Is it simple?
> One base I must install, must have it case insenitive
> br
> Henning
You can specify the collation for a database when you create it.
create database newdb ... collate <collation name> ...
You'll also have to watch out for joining between different collations if
change some but not others.
If you intend to create objects in your new database based on objects in
your old database, be sure to remove any collation clauses from your scripts
so that the newdb's default collation will be used instead of being
overridden by something in the scrip.
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
>> Thank You, simple as that :)
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
>> How do I see if my MS-SQL Server is case insensitive
>> One method:
>> IF 'A' = 'a'
>> PRINT 'case insensitive'
>> ELSE
>> PRINT 'case sensitive'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "H.Gjerde" <hg@.norspace.no> wrote in message
>> news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> How do I see if my MS-SQL Server is case insensitive
>> Br
>> Henning
>>
>>
>
Posted via a free Usenet account from http://www.teranews.com|||again, thank you
henning
"DH" <dh@.stargate.com> wrote in message
news:4682615b$0$7122$88260bb3@.free.teranews.com...
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:ub1Np9LuHHA.484@.TK2MSFTNGP06.phx.gbl...
>> My SQL is then case sensitive
>> How do I do-it case INsensitive? Is it simple?
>> One base I must install, must have it case insenitive
>> br
>> Henning
> You can specify the collation for a database when you create it.
> create database newdb ... collate <collation name> ...
> You'll also have to watch out for joining between different collations if
> change some but not others.
> If you intend to create objects in your new database based on objects in
> your old database, be sure to remove any collation clauses from your
> scripts so that the newdb's default collation will be used instead of
> being overridden by something in the scrip.
>
>> "H.Gjerde" <hg@.norspace.no> wrote in message
>> news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
>> Thank You, simple as that :)
>>
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
>> How do I see if my MS-SQL Server is case insensitive
>> One method:
>> IF 'A' = 'a'
>> PRINT 'case insensitive'
>> ELSE
>> PRINT 'case sensitive'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "H.Gjerde" <hg@.norspace.no> wrote in message
>> news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> How do I see if my MS-SQL Server is case insensitive
>> Br
>> Henning
>>
>>
>>
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

case insensitive

Hi
How do I see if my MS-SQL Server is case insensitive
Br
Henning
Check out sp_helpdb in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
Hi
How do I see if my MS-SQL Server is case insensitive
Br
Henning
|||> How do I see if my MS-SQL Server is case insensitive
One method:
IF 'A' = 'a'
PRINT 'case insensitive'
ELSE
PRINT 'case sensitive'
Hope this helps.
Dan Guzman
SQL Server MVP
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> Hi
> How do I see if my MS-SQL Server is case insensitive
> Br
> Henning
>
|||Thank You, simple as that

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
> One method:
> IF 'A' = 'a'
> PRINT 'case insensitive'
> ELSE
> PRINT 'case sensitive'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
>
|||My SQL is then case sensitive
How do I do-it case INsensitive? Is it simple?
One base I must install, must have it case insenitive
br
Henning
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
> Thank You, simple as that

>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
>
|||"H.Gjerde" <hg@.norspace.no> wrote in message
news:ub1Np9LuHHA.484@.TK2MSFTNGP06.phx.gbl...
> My SQL is then case sensitive
> How do I do-it case INsensitive? Is it simple?
> One base I must install, must have it case insenitive
> br
> Henning
You can specify the collation for a database when you create it.
create database newdb ... collate <collation name> ...
You'll also have to watch out for joining between different collations if
change some but not others.
If you intend to create objects in your new database based on objects in
your old database, be sure to remove any collation clauses from your scripts
so that the newdb's default collation will be used instead of being
overridden by something in the scrip.

> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
>
Posted via a free Usenet account from http://www.teranews.com
|||again, thank you
henning
"DH" <dh@.stargate.com> wrote in message
news:4682615b$0$7122$88260bb3@.free.teranews.com...
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:ub1Np9LuHHA.484@.TK2MSFTNGP06.phx.gbl...
> You can specify the collation for a database when you create it.
> create database newdb ... collate <collation name> ...
> You'll also have to watch out for joining between different collations if
> change some but not others.
> If you intend to create objects in your new database based on objects in
> your old database, be sure to remove any collation clauses from your
> scripts so that the newdb's default collation will be used instead of
> being overridden by something in the scrip.
>
>
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!
klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.
|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol
|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.
|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)
|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

case insensitive

Hi
How do I see if my MS-SQL Server is case insensitive
Br
HenningCheck out sp_helpdb in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
Hi
How do I see if my MS-SQL Server is case insensitive
Br
Henning|||> How do I see if my MS-SQL Server is case insensitive
One method:
IF 'A' = 'a'
PRINT 'case insensitive'
ELSE
PRINT 'case sensitive'
Hope this helps.
Dan Guzman
SQL Server MVP
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> Hi
> How do I see if my MS-SQL Server is case insensitive
> Br
> Henning
>|||Hi
Run sp_helpdb
in the result of sp_helpdb's status column if Collation says XX_CI_xx then
its case insensitive
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"H.Gjerde" <hg@.norspace.no> wrote in message
news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> Hi
> How do I see if my MS-SQL Server is case insensitive
> Br
> Henning
>|||Thank You, simple as that

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
> One method:
> IF 'A' = 'a'
> PRINT 'case insensitive'
> ELSE
> PRINT 'case sensitive'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:ua6aVELuHHA.3544@.TK2MSFTNGP03.phx.gbl...
>|||My SQL is then case sensitive
How do I do-it case INsensitive? Is it simple?
One base I must install, must have it case insenitive
br
Henning
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
> Thank You, simple as that

>
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:64F599D2-AEC8-44D6-949F-F06B3EB5CEF4@.microsoft.com...
>|||"H.Gjerde" <hg@.norspace.no> wrote in message
news:ub1Np9LuHHA.484@.TK2MSFTNGP06.phx.gbl...
> My SQL is then case sensitive
> How do I do-it case INsensitive? Is it simple?
> One base I must install, must have it case insenitive
> br
> Henning
You can specify the collation for a database when you create it.
create database newdb ... collate <collation name> ...
You'll also have to watch out for joining between different collations if
change some but not others.
If you intend to create objects in your new database based on objects in
your old database, be sure to remove any collation clauses from your scripts
so that the newdb's default collation will be used instead of being
overridden by something in the scrip.

> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:%23Ryjz0LuHHA.3688@.TK2MSFTNGP03.phx.gbl...
>
Posted via a free Usenet account from http://www.teranews.com|||again, thank you
henning
"DH" <dh@.stargate.com> wrote in message
news:4682615b$0$7122$88260bb3@.free.teranews.com...
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:ub1Np9LuHHA.484@.TK2MSFTNGP06.phx.gbl...
> You can specify the collation for a database when you create it.
> create database newdb ... collate <collation name> ...
> You'll also have to watch out for joining between different collations if
> change some but not others.
> If you intend to create objects in your new database based on objects in
> your old database, be sure to remove any collation clauses from your
> scripts so that the newdb's default collation will be used instead of
> being overridden by something in the scrip.
>
>
>
> --
> Posted via a free Usenet account from http://www.teranews.com
>sql

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspf...r />
tive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked t
o
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked t
o
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'

will return 'Burger King' ?!!!klabu wrote:

Quote:

Originally Posted by

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
>
will return 'Burger King' ?!!!
>
>

That depends on the collation being used...

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...

http://sqlserver2000.databases.aspf...-sensitive.html
"klabu" <klabu@.klabucomwrote in message
news:12m6h19lhljpr65@.corp.supernews.com...

Quote:

Originally Posted by

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
>
will return 'Burger King' ?!!!
>

|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:

Quote:

Originally Posted by

I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol

Glasshouse + stone:
'' IS NULL
'Hello' <'Hello '
Sequences, routines and tables share the same namespace

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html|||klabu wrote:

Quote:

Originally Posted by

I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol

Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:

Quote:

Originally Posted by

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
>
will return 'Burger King' ?!!!

Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.

Default when you install SQL Server is a case-insensitive collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:

Quote:

Originally Posted by

lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)

Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/F.../Forum2006.html

Tuesday, March 27, 2012

Case sensitivity problems...

Sunday, March 25, 2012

case sensitivity

Case sensitive/insensitive

Case Sensitive vs Insensitive

case sensitive to case insensitive

case sensitive to case insensitive

case sensitive to case insensitive

Case sensitive compare

Case sensitive compare

Case sensitive compare

Thursday, March 22, 2012

Case Insensitivity

case insensitive searching in sql server 2000 when its case sensitive

Tuesday, March 20, 2012

Case Insensitive Lookups?

case insensitive

case insensitive

case insensitive

case insensitive

case insensitive

case insensitive

case insensitive

uninstall SQL

Blog Archive

About Me