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