uninstall SQL: collation

Showing posts with label collation. Show all posts

Sunday, March 25, 2012

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 problem

You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensi..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:

>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
Hugo Kornelis, SQL Server MVP|||Thanks for these quick replies!!! I believe it works ;)
You are the best!|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

case sensitive collation issues

I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52
It's sitting on a server Collation = Latin1_General_BIN
The tempdb = Latin1_General_BIN
Note: I created the db from scratch on the server
When I run the following
CREATE PROCEDURE test
AS
DECALRE @.test VARCHAR(1)
SELECT @.TEST
Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
Must declare the variable '@.TEST'.
If I change the SELECT @.TEST to SELECT @.test it works fine.
It looks like it's a case issue , I thought the the db setting overrides the
the server setting and that any new objects created inherited the db
collation levels ?Hi Jack
In the Collate topic in BOL it says "The identifiers for variables, GOTO
labels, temporary stored procedures, and temporary tables are in the default
collation of the instance." Therefore the behaviour you see is as expected.
John
"Jack Vamvas" wrote:
> I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52
> It's sitting on a server Collation = Latin1_General_BIN
> The tempdb = Latin1_General_BIN
> Note: I created the db from scratch on the server
> When I run the following
> CREATE PROCEDURE test
> AS
> DECALRE @.test VARCHAR(1)
> SELECT @.TEST
> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
> Must declare the variable '@.TEST'.
>
> If I change the SELECT @.TEST to SELECT @.test it works fine.
>
> It looks like it's a case issue , I thought the the db setting overrides the
> the server setting and that any new objects created inherited the db
> collation levels ?
>
>
>
>|||Thanks
I've tried all sorts of things , as outlined below, so potentially it look
like the only solution is to either change the COLLATION level of the
instance or make the variables uniform in the sp.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@.microsoft.com...
> Hi Jack
> In the Collate topic in BOL it says "The identifiers for variables, GOTO
> labels, temporary stored procedures, and temporary tables are in the
> default
> collation of the instance." Therefore the behaviour you see is as
> expected.
> John
> "Jack Vamvas" wrote:
>> I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS,
>> SQLSortOrder=52
>> It's sitting on a server Collation = Latin1_General_BIN
>> The tempdb = Latin1_General_BIN
>> Note: I created the db from scratch on the server
>> When I run the following
>> CREATE PROCEDURE test
>> AS
>> DECALRE @.test VARCHAR(1)
>> SELECT @.TEST
>> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
>> Must declare the variable '@.TEST'.
>>
>> If I change the SELECT @.TEST to SELECT @.test it works fine.
>>
>> It looks like it's a case issue , I thought the the db setting overrides
>> the
>> the server setting and that any new objects created inherited the db
>> collation levels ?
>>
>>
>>|||Hi
Yes, as your instance is case sensitive then the variables will be as well.
As this is a compile error it is not so bad, and it is something you will
need to do if you don't have control of the live environment (e.g. if you
sell a product). Make sure that your coding standards cover this. If you use
source code control (at object level) it will help you track down when issues
due to collation problems are intorduced, and an automated build process
would also be a way of quickly catching this type of error (before you are
rushing to ship a release!)
HTH
John
"Jack Vamvas" wrote:
> Thanks
> I've tried all sorts of things , as outlined below, so potentially it look
> like the only solution is to either change the COLLATION level of the
> instance or make the variables uniform in the sp.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:AD1B6D99-C104-4F56-BCF8-4E87193FEF82@.microsoft.com...
> > Hi Jack
> >
> > In the Collate topic in BOL it says "The identifiers for variables, GOTO
> > labels, temporary stored procedures, and temporary tables are in the
> > default
> > collation of the instance." Therefore the behaviour you see is as
> > expected.
> >
> > John
> >
> > "Jack Vamvas" wrote:
> >
> >> I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS,
> >> SQLSortOrder=52
> >> It's sitting on a server Collation = Latin1_General_BIN
> >> The tempdb = Latin1_General_BIN
> >>
> >> Note: I created the db from scratch on the server
> >>
> >> When I run the following
> >>
> >> CREATE PROCEDURE test
> >> AS
> >> DECALRE @.test VARCHAR(1)
> >> SELECT @.TEST
> >>
> >> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
> >> Must declare the variable '@.TEST'.
> >>
> >>
> >> If I change the SELECT @.TEST to SELECT @.test it works fine.
> >>
> >>
> >> It looks like it's a case issue , I thought the the db setting overrides
> >> the
> >> the server setting and that any new objects created inherited the db
> >> collation levels ?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
>
>sql

case sensitive collation issues

I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52
It's sitting on a server Collation = Latin1_General_BIN
The tempdb = Latin1_General_BIN
Note: I created the db from scratch on the server
When I run the following
CREATE PROCEDURE test
AS
DECALRE @.test VARCHAR(1)
SELECT @.TEST
Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
Must declare the variable '@.TEST'.
If I change the SELECT @.TEST to SELECT @.test it works fine.
It looks like it's a case issue , I thought the the db setting overrides the
the server setting and that any new objects created inherited the db
collation levels ?did you spell declare incorrectly? However when I spell it correctly and run
your code, it works. Is your code sample complete?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt.com...
>I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS,
>SQLSortOrder=52
> It's sitting on a server Collation = Latin1_General_BIN
> The tempdb = Latin1_General_BIN
> Note: I created the db from scratch on the server
> When I run the following
> CREATE PROCEDURE test
> AS
> DECALRE @.test VARCHAR(1)
> SELECT @.TEST
> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
> Must declare the variable '@.TEST'.
>
> If I change the SELECT @.TEST to SELECT @.test it works fine.
>
> It looks like it's a case issue , I thought the the db setting overrides
> the
> the server setting and that any new objects created inherited the db
> collation levels ?
>
>
>
>|||Sorry,Yes , in the db the spelling is correct.
I have run the same code on another server where the server collation and
the db collation are the same , it works fine.
It's in the set up outlined below where the problem arises.
Does the tempdb collation create an issue?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O6zX7aE9GHA.1188@.TK2MSFTNGP05.phx.gbl...
> did you spell declare incorrectly? However when I spell it correctly and
> run your code, it works. Is your code sample complete?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt.com...
>>I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS,
>>SQLSortOrder=52
>> It's sitting on a server Collation = Latin1_General_BIN
>> The tempdb = Latin1_General_BIN
>> Note: I created the db from scratch on the server
>> When I run the following
>> CREATE PROCEDURE test
>> AS
>> DECALRE @.test VARCHAR(1)
>> SELECT @.TEST
>> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
>> Must declare the variable '@.TEST'.
>>
>> If I change the SELECT @.TEST to SELECT @.test it works fine.
>>
>> It looks like it's a case issue , I thought the the db setting overrides
>> the
>> the server setting and that any new objects created inherited the db
>> collation levels ?
>>
>>
>>
>|||Hi Jack
With tempdb using a different collation then you may have collation
conflicts when creating or joining to temporary tables. This can be overcome
by using the COLLATE option to force the collation when you either join to
the temporary table or when it is created.
John
"Jack Vamvas" wrote:
> Sorry,Yes , in the db the spelling is correct.
> I have run the same code on another server where the server collation and
> the db collation are the same , it works fine.
> It's in the set up outlined below where the problem arises.
> Does the tempdb collation create an issue?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O6zX7aE9GHA.1188@.TK2MSFTNGP05.phx.gbl...
> > did you spell declare incorrectly? However when I spell it correctly and
> > run your code, it works. Is your code sample complete?
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
> > news:rt2dnVE7BcPWIqXYnZ2dnUVZ8qWdnZ2d@.bt.com...
> >>I have a db - DB1 = Collation=SQL_Latin1_General_CP1_CI_AS,
> >>SQLSortOrder=52
> >> It's sitting on a server Collation = Latin1_General_BIN
> >> The tempdb = Latin1_General_BIN
> >>
> >> Note: I created the db from scratch on the server
> >>
> >> When I run the following
> >>
> >> CREATE PROCEDURE test
> >> AS
> >> DECALRE @.test VARCHAR(1)
> >> SELECT @.TEST
> >>
> >> Error = Server: Msg 137, Level 15, State 2, Procedure test, Line 5
> >> Must declare the variable '@.TEST'.
> >>
> >>
> >> If I change the SELECT @.TEST to SELECT @.test it works fine.
> >>
> >>
> >> It looks like it's a case issue , I thought the the db setting overrides
> >> the
> >> the server setting and that any new objects created inherited the db
> >> collation levels ?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

case sensitive collation issues

Thursday, March 22, 2012

case sensitive collation issues

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
LudekHi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can not
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:
> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
> > Hello,
> > SQL server 2000, database Test1, Collation name Czech_CS_AS
> > I have this table:
> > aa F1 aa
> > bb f1 bb
> > cc F1 cc
> > ***
> > SELECT TOP 8 Table1.*
> > FROM dbo.Table1 Table1
> > WHERE Table1.rozmer >= 'F1'
> > ORDER BY Table1.rozmer, Table1.nazev
> >
> > Result:
> > aa F1 aa
> > cc F1 cc
> > OK
> > ***
> > but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> > SELECT TOP 8 Table1.*
> > FROM dbo.Table1 Table1
> > WHERE Table1.rozmer+Table1.nazev >= 'F1'
> > ORDER BY Table1.rozmer, Table1.nazev
> >
> > Return all rows - also row with rozmer = 'f1' :
> > bb f1 bb
> > aa F1 aa
> > cc F1 cc
> >
> > Thank You for help
> > Ludek
> >
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>|||Hi Ludek
"Ludek" wrote:
> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with this
> 5 fields from all database + detailed view for one record. But database have
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first example?
Johnsql

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
Ludek
Hi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:

> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John
|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions
|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can not
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:

> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>
|||Hi Ludek
"Ludek" wrote:

> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with this
> 5 fields from all database + detailed view for one record. But database have
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first example?
John

Case sensitive

Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT
statement, i have to be case sensitive on the table name and field name. Is
there anywhere to ignore this so that i don't have to care about the case s
ensitivity? Is there any se
tting on the server that i can turn this off? Or is it a design by feature?On Tue, 16 Mar 2004 19:11:05 -0800, Loon wrote:

>Hi all, I'm using Thai_BIN collation for my database. When i build my SELECT statem
ent, i have to be case sensitive on the table name and field name. Is there anywhere
to ignore this so that i don't have to care about the case sensitivity? Is there an
y s
etting on the server that i can turn this off? Or is it a design by feature?
A binary sort order makes sorting and comparisons faster than any
other collation sequence can be, since SQL Server only has to compare
the byte values of the characters.
The drawback is that characters with different byte values (like 'A'
and 'a') compare unequal.
The only way to change collation sequence after installation is to
rebuild the master database and then recreate all your databases with
the new collation sequence. Refer to BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Case sensitive

Hello,
SQL server 2000, database Test1, Collation name Czech_CS_AS
I have this table:
aa F1 aa
bb f1 bb
cc F1 cc
***
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Result:
aa F1 aa
cc F1 cc
OK
***
but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
SELECT TOP 8 Table1.*
FROM dbo.Table1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
Return all rows - also row with rozmer = 'f1' :
bb f1 bb
aa F1 aa
cc F1 cc
Thank You for help
LudekHi Ludek
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data that would be useful in answering your question.
"Ludek" wrote:

> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
>
For example:
USE TEMPDB
GO
CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
rozmer varchar(10) COLLATE Czech_CS_AS,
other varchar(10) COLLATE Czech_CS_AS )
GO
INSERT INTO Test1( nazev, rozmer, other )
SELECT 'aa', 'F1', 'aa'
UNION ALL SELECT 'bb', 'f1', 'bb'
UNION ALL SELECT 'cc', 'F1', 'cc'
Then your query:
SELECT TOP 8 Table1.*
FROM dbo.Test1 Table1
WHERE Table1.rozmer >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other
-- -- --
aa F1 aa
cc F1 cc
(2 row(s) affected)
*/
As you expected:
And the second query:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev >= 'F1'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
bb f1 bb f1bb
aa F1 aa F1aa
cc F1 cc F1cc
(3 row(s) affected)
*/
If you only wanted those values where rozmer was 'F1' then you could have
written:
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
ORDER BY Table1.rozmer, Table1.nazev
/*
nazev rozmer other Concatenation
-- -- -- --
aa F1 aa F1aa
cc F1 cc F1cc
(2 row(s) affected)
*/
If this is actually because you are not expecting f1bb, then you will need
to know that f1bb is greater than F1, to show this you may want to look at
their actual ordering e.g.
SELECT *
FROM (
SELECT rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
f1
F1
F1
F1aa
f1bb
F1cc
(6 row(s) affected)
*/
Which shows F1cc > f1bb > F1aa > F1 > f1
You may be confussion the binary ordering which would be
SELECT *
FROM (
SELECT rozmer COLLATE Czech_BIN AS rozmer
FROM dbo.Test1
UNION ALL
SELECT rozmer+nazev COLLATE Czech_BIN
FROM dbo.Test1
) A
ORDER BY rozmer
/*
rozmer
--
F1
F1
F1aa
F1cc
f1
f1bb
(6 row(s) affected)
*/
Which shows f1bb > f1 > F1cc > F1aa > F1
and so you could do a query such as
SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
FROM dbo.Test1 Table1
WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
ORDER BY Table1.rozmer, Table1.nazev
John|||On May 22, 11:16 am, Ludek <L...@.discussions.microsoft.com> wrote:
> Hello,
> SQL server 2000, database Test1, Collation name Czech_CS_AS
> I have this table:
> aa F1 aa
> bb f1 bb
> cc F1 cc
> ***
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Result:
> aa F1 aa
> cc F1 cc
> OK
> ***
> but next select - I need concate column WHERE Table1.rozmer+Table1.nazev
> SELECT TOP 8 Table1.*
> FROM dbo.Table1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> Return all rows - also row with rozmer = 'f1' :
> bb f1 bb
> aa F1 aa
> cc F1 cc
> Thank You for help
> Ludek
You can use UPPER or LOWER functions|||Hello John,
thank You for help.
I must perhaps change application strategy. I have master form with grid
with recordsource view - this view have 8 records. Next 8 or previous 8
record I get from database with requery view. But in case-sensitive I can no
t
this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
key. Create any cascade request is complicated. I will create view with this
5 fields from all database + detailed view for one record. But database have
30000 rows.
Is this way?
Thank You Ludek
"John Bell" wrote:

> Hi Ludek
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data that would be useful in answering your question.
> "Ludek" wrote:
>
> For example:
> USE TEMPDB
> GO
> CREATE TABLE Test1 ( nazev varchar(10) COLLATE Czech_CS_AS,
> rozmer varchar(10) COLLATE Czech_CS_AS,
> other varchar(10) COLLATE Czech_CS_AS )
> GO
> INSERT INTO Test1( nazev, rozmer, other )
> SELECT 'aa', 'F1', 'aa'
> UNION ALL SELECT 'bb', 'f1', 'bb'
> UNION ALL SELECT 'cc', 'F1', 'cc'
> Then your query:
> SELECT TOP 8 Table1.*
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other
> -- -- --
> aa F1 aa
> cc F1 cc
> (2 row(s) affected)
> */
> As you expected:
> And the second query:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev >= 'F1'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> bb f1 bb f1bb
> aa F1 aa F1aa
> cc F1 cc F1cc
> (3 row(s) affected)
> */
> If you only wanted those values where rozmer was 'F1' then you could have
> written:
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev LIKE 'F1%'
> ORDER BY Table1.rozmer, Table1.nazev
> /*
> nazev rozmer other Concatenation
> -- -- -- --
> aa F1 aa F1aa
> cc F1 cc F1cc
> (2 row(s) affected)
> */
> If this is actually because you are not expecting f1bb, then you will need
> to know that f1bb is greater than F1, to show this you may want to look at
> their actual ordering e.g.
> SELECT *
> FROM (
> SELECT rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> f1
> F1
> F1
> F1aa
> f1bb
> F1cc
> (6 row(s) affected)
> */
> Which shows F1cc > f1bb > F1aa > F1 > f1
> You may be confussion the binary ordering which would be
> SELECT *
> FROM (
> SELECT rozmer COLLATE Czech_BIN AS rozmer
> FROM dbo.Test1
> UNION ALL
> SELECT rozmer+nazev COLLATE Czech_BIN
> FROM dbo.Test1
> ) A
> ORDER BY rozmer
> /*
> rozmer
> --
> F1
> F1
> F1aa
> F1cc
> f1
> f1bb
> (6 row(s) affected)
> */
> Which shows f1bb > f1 > F1cc > F1aa > F1
> and so you could do a query such as
> SELECT TOP 8 Table1.* ,Table1.rozmer+Table1.nazev as [Concatenation]
> FROM dbo.Test1 Table1
> WHERE Table1.rozmer+Table1.nazev COLLATE Czech_BIN >= 'F1'
> AND Table1.rozmer+Table1.nazev COLLATE Czech_BIN <= 'F1zz'
> ORDER BY Table1.rozmer, Table1.nazev
> John
>|||Hi Ludek
"Ludek" wrote:

> Hello John,
> thank You for help.
> I must perhaps change application strategy. I have master form with grid
> with recordsource view - this view have 8 records. Next 8 or previous 8
> record I get from database with requery view. But in case-sensitive I can
not
> this use - F1cc > f1bb > F1aa > F1 > f1. I must concate 5 field for unique
> key. Create any cascade request is complicated. I will create view with th
is
> 5 fields from all database + detailed view for one record. But database ha
ve
> 30000 rows.
> Is this way?
> Thank You Ludek
The result you have are as expected, although it doesn't seem to be the one
you want! If you posted sample data and expected results it may be clearer
how you should write your query, for instance why you need to test the
concetenated string and not just your rozmer column as per your first exampl
e?
John

Sunday, March 25, 2012

Case Sensitive vs Insensitive

Case sensitive problem

case sensitive collation issues

case sensitive collation issues

case sensitive collation issues

Thursday, March 22, 2012

case sensitive collation issues

case sensitive collation issues

Case sensitive

Case sensitive

Case sensitive

Case sensitive

uninstall SQL

Blog Archive

About Me