Hey eb
Whats the point in having a collation attached to a unicode
charecter typed column'
If you want to set a specific collation to character data, why
make it unicode on the first place'
Am I missing something here'
Thanks
ReaCollation isn't only the character set. It is also sorting order, case sensitivity, accent
sensitivity etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rea" <Rea@.discussions.microsoft.com> wrote in message
news:CA4DF029-A399-4FE0-B198-C7FC621F0691@.microsoft.com...
> Hey eb
> Whats the point in having a collation attached to a unicode
> charecter typed column'
> If you want to set a specific collation to character data, why
> make it unicode on the first place'
> Am I missing something here'
> Thanks
> Rea
>
>sqlsql
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Tuesday, March 27, 2012
collation on nchar column
Hey eb
Whats the point in having a collation attached to a unicode
charecter typed column'
If you want to set a specific collation to character data, why
make it unicode on the first place'
Am I missing something here'
Thanks
ReaCollation isn't only the character set. It is also sorting order, case sensi
tivity, accent
sensitivity etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rea" <Rea@.discussions.microsoft.com> wrote in message
news:CA4DF029-A399-4FE0-B198-C7FC621F0691@.microsoft.com...
> Hey eb
> Whats the point in having a collation attached to a unicode
> charecter typed column'
> If you want to set a specific collation to character data, why
> make it unicode on the first place'
> Am I missing something here'
> Thanks
> Rea
>
>
Whats the point in having a collation attached to a unicode
charecter typed column'
If you want to set a specific collation to character data, why
make it unicode on the first place'
Am I missing something here'
Thanks
ReaCollation isn't only the character set. It is also sorting order, case sensi
tivity, accent
sensitivity etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rea" <Rea@.discussions.microsoft.com> wrote in message
news:CA4DF029-A399-4FE0-B198-C7FC621F0691@.microsoft.com...
> Hey eb
> Whats the point in having a collation attached to a unicode
> charecter typed column'
> If you want to set a specific collation to character data, why
> make it unicode on the first place'
> Am I missing something here'
> Thanks
> Rea
>
>
Sunday, March 25, 2012
collation name
Is there any way, I can query across all objects in a given database for what is the current collation name for each column in an object?select [Object_Name]=object_name(id),
[Column Name] = name,
[Collation] = collation
from syscolumns
where objectproperty(id, 'ismsshipped') = 0
order by object_name(id), colid
[Column Name] = name,
[Collation] = collation
from syscolumns
where objectproperty(id, 'ismsshipped') = 0
order by object_name(id), colid
Collation issue
Hi,
I have a simple issue with collation but not able to find a quick fix or solution. I have a simple name column and I want the search on this column to NOT ignore the blank spaces in the end. For example, SELECT * FROM Table1 WHERE name='all ' should not return me any rows where as SELECT * FROM Table1 WHERE name='all' should return me the rows. I tried setting windows collation and made case sensitive, accent sensitive and width sensitive but it doesnt seem to work. (I dont want to trim the text)
Any suggestions?
Shyam
May be adding symbol at the end could help:
select * from #Table1 where name+'|'='all '+'|'
|||I would rather trim it but I dont want to touch the sql query now. Any suggestions? in terms of settings/configuration at the sql server level?
Shyam
|||Hi this is not a collation issue.
It is a specification. You can't achive your desired result with out changing your query. It is a ANSI/ISO SQL-92 specification. As per the specification the len('Abc') = len('Abc ') = len('Abc ') = 3. The trailing space are not counted.
The following query may fix your issue..
SELECT * FROM TABLE WHERE Convert(varbinary,COL1) = convert(varbinary,'')
Thursday, March 22, 2012
collation ansi padding and trailing blanks
Hi,
This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?
I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...
The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...
I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...
Cany somebody please explain why they all return two rows?
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t1
where varcharcol='Trailing blank ';
GO
PRINT 'Testing with ANSI_PADDING OFF';
SET ANSI_PADDING OFF;
GO
CREATE TABLE t2 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t2 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
INSERT INTO t2 VALUES ('Trailing blank ', 'Trailing blank', 0x00ee00);
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank';
GO
SELECT 'CHAR' = '>' + charcol + '<', 'VARCHAR'='>' + varcharcol + '<',
varbinarycol
FROM t2
where varcharcol='Trailing blank ';
GO
DROP TABLE t1
DROP TABLE t2
ANSI padding setting only affects the storage and how the trimming of blanks is performed for non-unicode data. It doesn't change the search semantics. SQL Server will always ignore trailing blanks / spaces for equality searches. If you perform the same using LIKE then trailing blanks will be considered. If you do the query below after inserting the data, you will see how the storage differs when ANSI_PADDING is ON and OFF.
select datalength(charcol), datalength(varcharcol)
from t1
select datalength(charcol), datalength(varcharcol)
from t2
Collation and tilde (~)
I have a table with a char(7) column with values like this:
0000100
0000200
ABC
DEF
~000300
~000400
When I use the default SQL Server collation, the values starting with the ~
sort above the "ABC" and "DEF" values (unlike several other database servers
I have used). I want those values to come last when ordering by that
column.
So far, the only collation I have come up with (I've tried several) that
does that is Latin1_General_BIN. But that makes the column case sensitive.
Is there another collation that would sort the tilde last, but provide case
insensitive comparisions on the column?I don't know of a collation which does this, but if sorting the resultset if
your requirement, assuming you have no values with 'ZZZZZZZ', you can just
do:
ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
Anith|||Unfortunately, the program has to work across different database servers, so
we do not want to use SQL Server-specific queries if we really do not have
to.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
> I don't know of a collation which does this, but if sorting the resultset
if
> your requirement, assuming you have no values with 'ZZZZZZZ', you can just
> do:
> ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
> --
> Anith
>|||Hello JJ,
I guess the only way to find the right collation to meet your needs
is to try it out different collations (as you might have already done
it). If you can't, then as Anith
has pointed out you will have to modify the queries to meet the
sorting needs.
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "JJ" <jjjj@.nospam.com>
>References: <O6#ZVHqCEHA.2656@.TK2MSFTNGP12.phx.gbl>
<OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl>
>Subject: Re: Collation and tilde (~)
>Date: Mon, 15 Mar 2004 14:21:21 -0500
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <#$do0KsCEHA.308@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 146.145.51.166
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
FTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:333939
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Unfortunately, the program has to work across different database
servers, so
>we do not want to use SQL Server-specific queries if we really do
not have
>to.
>"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
resultset
>if
can just
>
>
0000100
0000200
ABC
DEF
~000300
~000400
When I use the default SQL Server collation, the values starting with the ~
sort above the "ABC" and "DEF" values (unlike several other database servers
I have used). I want those values to come last when ordering by that
column.
So far, the only collation I have come up with (I've tried several) that
does that is Latin1_General_BIN. But that makes the column case sensitive.
Is there another collation that would sort the tilde last, but provide case
insensitive comparisions on the column?I don't know of a collation which does this, but if sorting the resultset if
your requirement, assuming you have no values with 'ZZZZZZZ', you can just
do:
ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
Anith|||Unfortunately, the program has to work across different database servers, so
we do not want to use SQL Server-specific queries if we really do not have
to.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
> I don't know of a collation which does this, but if sorting the resultset
if
> your requirement, assuming you have no values with 'ZZZZZZZ', you can just
> do:
> ORDER BY CASE WHEN LEFT(col, 1) = '~' THEN REPLICATE('Z', 7) END
> --
> Anith
>|||Hello JJ,
I guess the only way to find the right collation to meet your needs
is to try it out different collations (as you might have already done
it). If you can't, then as Anith
has pointed out you will have to modify the queries to meet the
sorting needs.
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "JJ" <jjjj@.nospam.com>
>References: <O6#ZVHqCEHA.2656@.TK2MSFTNGP12.phx.gbl>
<OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl>
>Subject: Re: Collation and tilde (~)
>Date: Mon, 15 Mar 2004 14:21:21 -0500
>Lines: 19
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <#$do0KsCEHA.308@.TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 146.145.51.166
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXS01.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MS
FTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:333939
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Unfortunately, the program has to work across different database
servers, so
>we do not want to use SQL Server-specific queries if we really do
not have
>to.
>"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
>news:OM1b8WrCEHA.2620@.TK2MSFTNGP12.phx.gbl...
resultset
>if
can just
>
>
Tuesday, March 20, 2012
collation
hi,
i want to delete all column collation in database !
how can i do ?
thank'sEvery column of a string datatype has a collation associated with it. You cannot "delete the
collation" without also removing that column. Can you elaborate on what you want to achieve?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ch." <trotroyanas@.hotmail.com> wrote in message news:Ooimr$C0GHA.1292@.TK2MSFTNGP03.phx.gbl...
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||Hi,
If the problem is that the column has 1 type of collation specified for it,
and you wish to change it (e.g. to the default collation for the database),
then the following might help ...
ALTER TABLE <tablename>
ALTER COLUMN <name>
<datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
... <other column options>
GO
Sources of information for this include 'Collations, changing' in SQL 2000
BOL and 'How To: Set Column Collation' in SQL 2005 BOL
Collation can be changed both through T-SQK or in SSMS
Hope the above helps
Craig
"Ch." wrote:
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||yes it's my problem
i have make script to drop index
and alter procedure for all column have a collation but i have new problem !
i want to re create a index (drop before the alter)
how can i do, for scripting create index of tables ?
i know a table who have à index dropped !
i search a script who waiting table in parameters and return
script to create index ? (before drop of course)
!
"craig_amtdatatechnologies@.discussions.mi"
<craigamtdatatechnologiesdiscussionsmi@.discussions.microsoft.com> a écrit
dans le message de news:
7C701026-9D52-4DBE-A425-43109BE4D0C3@.microsoft.com...
> Hi,
> If the problem is that the column has 1 type of collation specified for
> it,
> and you wish to change it (e.g. to the default collation for the
> database),
> then the following might help ...
>
> ALTER TABLE <tablename>
> ALTER COLUMN <name>
> <datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
> ... <other column options>
> GO
> Sources of information for this include 'Collations, changing' in SQL 2000
> BOL and 'How To: Set Column Collation' in SQL 2005 BOL
> Collation can be changed both through T-SQK or in SSMS
> Hope the above helps
> Craig
>
> "Ch." wrote:
>> hi,
>> i want to delete all column collation in database !
>> how can i do ?
>> thank's
>>
>>
i want to delete all column collation in database !
how can i do ?
thank'sEvery column of a string datatype has a collation associated with it. You cannot "delete the
collation" without also removing that column. Can you elaborate on what you want to achieve?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ch." <trotroyanas@.hotmail.com> wrote in message news:Ooimr$C0GHA.1292@.TK2MSFTNGP03.phx.gbl...
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||Hi,
If the problem is that the column has 1 type of collation specified for it,
and you wish to change it (e.g. to the default collation for the database),
then the following might help ...
ALTER TABLE <tablename>
ALTER COLUMN <name>
<datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
... <other column options>
GO
Sources of information for this include 'Collations, changing' in SQL 2000
BOL and 'How To: Set Column Collation' in SQL 2005 BOL
Collation can be changed both through T-SQK or in SSMS
Hope the above helps
Craig
"Ch." wrote:
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||yes it's my problem
i have make script to drop index
and alter procedure for all column have a collation but i have new problem !
i want to re create a index (drop before the alter)
how can i do, for scripting create index of tables ?
i know a table who have à index dropped !
i search a script who waiting table in parameters and return
script to create index ? (before drop of course)
!
"craig_amtdatatechnologies@.discussions.mi"
<craigamtdatatechnologiesdiscussionsmi@.discussions.microsoft.com> a écrit
dans le message de news:
7C701026-9D52-4DBE-A425-43109BE4D0C3@.microsoft.com...
> Hi,
> If the problem is that the column has 1 type of collation specified for
> it,
> and you wish to change it (e.g. to the default collation for the
> database),
> then the following might help ...
>
> ALTER TABLE <tablename>
> ALTER COLUMN <name>
> <datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
> ... <other column options>
> GO
> Sources of information for this include 'Collations, changing' in SQL 2000
> BOL and 'How To: Set Column Collation' in SQL 2005 BOL
> Collation can be changed both through T-SQK or in SSMS
> Hope the above helps
> Craig
>
> "Ch." wrote:
>> hi,
>> i want to delete all column collation in database !
>> how can i do ?
>> thank's
>>
>>
Collation
Hi
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> Ã?kergatanÃ? 4Ã?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUIÃ?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
ArunWhy do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ÅkergatanÅ 4Å2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUIÑONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> Ã?kergatanÃ? 4Ã?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUIÃ?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readable
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if there
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> > Hi
> > I have a table with a column as NVARCHAR(500).
> > The column has some values like this:
> > ROW1=> Ã?kergatanÃ? 4Ã?2
> > ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> > ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> > ROW4=> MARJORIE DE QUIÃ?ONEZ
> >
> > I want to avoid the UNICODE character set as seen above. Can anyone please
> > advice what Collation should I set for that column?
> >
> > Thanks in advance,
> > Arun
>
>|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
> > Why do you want to avoid the UNICODE? What do you want to convert all
of
> > those characters to?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> > news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> > > Hi
> > > I have a table with a column as NVARCHAR(500).
> > > The column has some values like this:
> > > ROW1=> ÅkergatanÅ 4Å2
> > > ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> > > ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> > > ROW4=> MARJORIE DE QUIÑONEZ
> > >
> > > I want to avoid the UNICODE character set as seen above. Can anyone
please
> > > advice what Collation should I set for that column?
> > >
> > > Thanks in advance,
> > > Arun
> >
> >
> >
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> Ã?kergatanÃ? 4Ã?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUIÃ?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
ArunWhy do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ÅkergatanÅ 4Å2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUIÑONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> Ã?kergatanÃ? 4Ã?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUIÃ?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readable
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if there
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> > Hi
> > I have a table with a column as NVARCHAR(500).
> > The column has some values like this:
> > ROW1=> Ã?kergatanÃ? 4Ã?2
> > ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> > ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> > ROW4=> MARJORIE DE QUIÃ?ONEZ
> >
> > I want to avoid the UNICODE character set as seen above. Can anyone please
> > advice what Collation should I set for that column?
> >
> > Thanks in advance,
> > Arun
>
>|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
> > Why do you want to avoid the UNICODE? What do you want to convert all
of
> > those characters to?
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> > news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> > > Hi
> > > I have a table with a column as NVARCHAR(500).
> > > The column has some values like this:
> > > ROW1=> ÅkergatanÅ 4Å2
> > > ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> > > ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> > > ROW4=> MARJORIE DE QUIÑONEZ
> > >
> > > I want to avoid the UNICODE character set as seen above. Can anyone
please
> > > advice what Collation should I set for that column?
> > >
> > > Thanks in advance,
> > > Arun
> >
> >
> >
Collation
Hi
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> ?kergatan? 4?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUI?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
Arun
Why do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> kergatan 42
> ROW2=> TORRE CUSCATLAN 6 NIVEL = >
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1 PL
> ROW4=> MARJORIE DE QUIONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun
|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/de...ar_da_6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ?kergatan? 4?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUI?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun
|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readable
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if there
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
>
>
|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there[vbcol=seagreen]
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
of[vbcol=seagreen]
please[vbcol=seagreen]
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> ?kergatan? 4?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUI?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
Arun
Why do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> kergatan 42
> ROW2=> TORRE CUSCATLAN 6 NIVEL = >
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1 PL
> ROW4=> MARJORIE DE QUIONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun
|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/de...ar_da_6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ?kergatan? 4?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUI?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun
|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readable
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if there
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
>
>
|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there[vbcol=seagreen]
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
of[vbcol=seagreen]
please[vbcol=seagreen]
collation
hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
Michael Zdarsky
They aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>
|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
Michael Zdarsky
They aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>
|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
Collation
I have a column in SQL Server table which needs to be Case Sensitive to where
clause in queries
i know this can be changed by changing the collation
Can someone give me the steps to do this
Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
Also how do you find collation of database, table or column
i do it by reverse engineering the scipt using Enterprise MAnager, is there
a command ?
Thanks
You can change collation for a column using ALTER TABLE... ALTER COLUMN... More information in Books
Online.
> Also how do you find collation of database, table or column
Database: Use the DATABASEPROPERTYEX() function.
Table doesn't have a collation.
Column: sp_help
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:601D7057-9434-40F2-8BD9-335F6D68C84A@.microsoft.com...
> I have a column in SQL Server table which needs to be Case Sensitive to where
> clause in queries
> i know this can be changed by changing the collation
> Can someone give me the steps to do this
> Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
> Also how do you find collation of database, table or column
> i do it by reverse engineering the scipt using Enterprise MAnager, is there
> a command ?
> Thanks
>
clause in queries
i know this can be changed by changing the collation
Can someone give me the steps to do this
Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
Also how do you find collation of database, table or column
i do it by reverse engineering the scipt using Enterprise MAnager, is there
a command ?
Thanks
You can change collation for a column using ALTER TABLE... ALTER COLUMN... More information in Books
Online.
> Also how do you find collation of database, table or column
Database: Use the DATABASEPROPERTYEX() function.
Table doesn't have a collation.
Column: sp_help
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:601D7057-9434-40F2-8BD9-335F6D68C84A@.microsoft.com...
> I have a column in SQL Server table which needs to be Case Sensitive to where
> clause in queries
> i know this can be changed by changing the collation
> Can someone give me the steps to do this
> Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
> Also how do you find collation of database, table or column
> i do it by reverse engineering the scipt using Enterprise MAnager, is there
> a command ?
> Thanks
>
Collation
I have a column in SQL Server table which needs to be Case Sensitive to where
clause in queries
i know this can be changed by changing the collation
Can someone give me the steps to do this
Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
Also how do you find collation of database, table or column
i do it by reverse engineering the scipt using Enterprise MAnager, is there
a command ?
ThanksYou can change collation for a column using ALTER TABLE... ALTER COLUMN... More information in Books
Online.
> Also how do you find collation of database, table or column
Database: Use the DATABASEPROPERTYEX() function.
Table doesn't have a collation.
Column: sp_help
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:601D7057-9434-40F2-8BD9-335F6D68C84A@.microsoft.com...
> I have a column in SQL Server table which needs to be Case Sensitive to where
> clause in queries
> i know this can be changed by changing the collation
> Can someone give me the steps to do this
> Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
> Also how do you find collation of database, table or column
> i do it by reverse engineering the scipt using Enterprise MAnager, is there
> a command ?
> Thanks
>
clause in queries
i know this can be changed by changing the collation
Can someone give me the steps to do this
Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
Also how do you find collation of database, table or column
i do it by reverse engineering the scipt using Enterprise MAnager, is there
a command ?
ThanksYou can change collation for a column using ALTER TABLE... ALTER COLUMN... More information in Books
Online.
> Also how do you find collation of database, table or column
Database: Use the DATABASEPROPERTYEX() function.
Table doesn't have a collation.
Column: sp_help
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:601D7057-9434-40F2-8BD9-335F6D68C84A@.microsoft.com...
> I have a column in SQL Server table which needs to be Case Sensitive to where
> clause in queries
> i know this can be changed by changing the collation
> Can someone give me the steps to do this
> Right now it has default SQL Server collation SQL_Latin1_General_Cp1_CI_AS
> Also how do you find collation of database, table or column
> i do it by reverse engineering the scipt using Enterprise MAnager, is there
> a command ?
> Thanks
>
collation
hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
--
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)
collation
hello,
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)sqlsql
i have to migrate and old schema into a new one.
i have the old table column with collation
Latin1_General_CI_AS
and the new table column with collation
Latin1_General_CP1_CI_AS
both columns are nvarchar
when i transfer the data i get the error
cannot resolve collation conflict for equal to operation
I do not understand this I read tens or hundreds of documentation
documentation says there is no conflict when unsing unicode types like
nvarchar.
but there is a conflict?
why and how can I solve it?
thank you.
michael
--
Michael ZdarskyThey aren't the same collation hence the message.
You can use the COLLATE option to cast the collation from one to another.
The 'n' in nvarchar just allows you to store double byte characters for
chinese etc... nothing to do with collation as such, its the fact its a
string data type that has a baring on the collation.
Tony.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)|||hello tony
thank you for your answer,
yes they are different, but I compared the collation properties
codepage, lcid and comparison style and they are all identical.
so only the name is different, but there is still the problem.
how does the server compare this collations?
thank you
michael
"Tony Rogerson" wrote:
> They aren't the same collation hence the message.
> You can use the COLLATE option to cast the collation from one to another.
> The 'n' in nvarchar just allows you to store double byte characters for
> chinese etc... nothing to do with collation as such, its the fact its a
> string data type that has a baring on the collation.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://www.sqlserverfaq.com?mbr=21
> (Create your own groups, Forum, FAQ's and a ton more)
>
>|||Hi Michael,
Seriously, they are different - one is a Windows collation and one SQL.
On the SQL Server set up you get the option of using a Windows collation or
SQL, one for backwards compatibility - can't remember which ones which now,
but this is where collation problems usually start.
Collation is horrible, the set-up doesn't really help you much either.
sp_helpsort can be used to get more information on the collation you are
using.
print cast( databasepropertyex( 'master', 'collation' ) as varchar(128) )
The above statement can be used to determine the database collation.
print cast( databasepropertyex( 'master', 'SQLSortOrder' ) as varchar(128) )
The above can be used to get the server sort id, which is what will differ.
Hope that helps.
Tony Rogerson
SQL Server MVP
http://www.sqlserverfaq.com?mbr=21
(Create your own groups, Forum, FAQ's and a ton more)sqlsql
Collation
Hi
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> ?kergatan? 4?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUI?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
ArunWhy do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> kergatan 42
> ROW2=> TORRE CUSCATLAN 6 NIVEL = >
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1 PL
> ROW4=> MARJORIE DE QUIONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/d...br />
6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ?kergatan? 4?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUI?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readabl
e
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if ther
e
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
>
>|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there[vbcol=seagreen]
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
>
of[vbcol=seagreen]
please[vbcol=seagreen]sqlsql
I have a table with a column as NVARCHAR(500).
The column has some values like this:
ROW1=> ?kergatan? 4?2
ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
ROW4=> MARJORIE DE QUI?ONEZ
I want to avoid the UNICODE character set as seen above. Can anyone please
advice what Collation should I set for that column?
Thanks in advance,
ArunWhy do you want to avoid the UNICODE? What do you want to convert all of
those characters to?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> kergatan 42
> ROW2=> TORRE CUSCATLAN 6 NIVEL = >
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1 PL
> ROW4=> MARJORIE DE QUIONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||Unfortunaly if you want to have different languages within a database table
then unicode is the way to go.
The reason is that a collation is normally specific for a country so if you
change it to one collation then you may find your character change.
Can you please tell us why you no longer want a unicode field ?
In the meanwhile have a look at this
http://msdn.microsoft.com/library/d...br />
6ttf.asp
Peter
"Only two things are infinite, the universe and human stupidity, and I'm not
sure about the former."
Albert Einstein
"Arun Shankar" wrote:
> Hi
> I have a table with a column as NVARCHAR(500).
> The column has some values like this:
> ROW1=> ?kergatan? 4?2
> ROW2=> TORRE CUSCATLAN 6° NIVEL = > °
> ROW3=> EX. EDIF. ANTEL CENTRO DE GOBIERNO 1° PL
> ROW4=> MARJORIE DE QUI?ONEZ
> I want to avoid the UNICODE character set as seen above. Can anyone please
> advice what Collation should I set for that column?
> Thanks in advance,
> Arun|||I will be using XML to create reports from those columns. XML supports UTF8
character set and its not able to create reports with this data. All I want
to do is avoid those special characters in that column and made some readabl
e
set of data. I am assuming changing the Collation for that Column will
resolve the issue. Please correct me if I am wrong. Also let me know if ther
e
is any other way to do this.
Thanks,
Arun
"Adam Machanic" wrote:
> Why do you want to avoid the UNICODE? What do you want to convert all of
> those characters to?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
> news:185DBA54-A6AA-4786-BACA-2E48B97C36A2@.microsoft.com...
>
>|||Probably changing the column to VARCHAR, rather than altering the collation.
But I'm not sure what this will do with the 2-byte characters.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Arun Shankar" <ArunShankar@.discussions.microsoft.com> wrote in message
news:435E4B78-DC0B-48BD-9D67-B9E5B8013814@.microsoft.com...
> I will be using XML to create reports from those columns. XML supports
UTF8
> character set and its not able to create reports with this data. All I
want
> to do is avoid those special characters in that column and made some
readable
> set of data. I am assuming changing the Collation for that Column will
> resolve the issue. Please correct me if I am wrong. Also let me know if
there[vbcol=seagreen]
> is any other way to do this.
> Thanks,
> Arun
> "Adam Machanic" wrote:
>
of[vbcol=seagreen]
please[vbcol=seagreen]sqlsql
collation
hi,
i want to delete all column collation in database !
how can i do ?
thank'sEvery column of a string datatype has a collation associated with it. You ca
nnot "delete the
collation" without also removing that column. Can you elaborate on what you
want to achieve?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ch." <trotroyanas@.hotmail.com> wrote in message news:Ooimr$C0GHA.1292@.TK2MSFTNGP03.phx.gbl.
.
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||Hi,
If the problem is that the column has 1 type of collation specified for it,
and you wish to change it (e.g. to the default collation for the database),
then the following might help ...
ALTER TABLE <tablename>
ALTER COLUMN <name>
<datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
... <other column options>
GO
Sources of information for this include 'Collations, changing' in SQL 2000
BOL and 'How To: Set Column Collation' in SQL 2005 BOL
Collation can be changed both through T-SQK or in SSMS
Hope the above helps
Craig
"Ch." wrote:
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||yes it's my problem
i have make script to drop index
and alter procedure for all column have a collation but i have new problem !
i want to re create a index (drop before the alter)
how can i do, for scripting create index of tables ?
i know a table who have index dropped !
i search a script who waiting table in parameters and return
script to create index ? (before drop of course)
!
"craig_amtdatatechnologies@.discussions.mi"
< craigamtdatatechnologiesdiscussionsmi@.di
scussions.microsoft.com> a crit
dans le message de news:
7C701026-9D52-4DBE-A425-43109BE4D0C3@.microsoft.com...[vbcol=seagreen]
> Hi,
> If the problem is that the column has 1 type of collation specified for
> it,
> and you wish to change it (e.g. to the default collation for the
> database),
> then the following might help ...
>
> ALTER TABLE <tablename>
> ALTER COLUMN <name>
> <datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
> ... <other column options>
> GO
> Sources of information for this include 'Collations, changing' in SQL 2000
> BOL and 'How To: Set Column Collation' in SQL 2005 BOL
> Collation can be changed both through T-SQK or in SSMS
> Hope the above helps
> Craig
>
> "Ch." wrote:
>
i want to delete all column collation in database !
how can i do ?
thank'sEvery column of a string datatype has a collation associated with it. You ca
nnot "delete the
collation" without also removing that column. Can you elaborate on what you
want to achieve?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ch." <trotroyanas@.hotmail.com> wrote in message news:Ooimr$C0GHA.1292@.TK2MSFTNGP03.phx.gbl.
.
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||Hi,
If the problem is that the column has 1 type of collation specified for it,
and you wish to change it (e.g. to the default collation for the database),
then the following might help ...
ALTER TABLE <tablename>
ALTER COLUMN <name>
<datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
... <other column options>
GO
Sources of information for this include 'Collations, changing' in SQL 2000
BOL and 'How To: Set Column Collation' in SQL 2005 BOL
Collation can be changed both through T-SQK or in SSMS
Hope the above helps
Craig
"Ch." wrote:
> hi,
> i want to delete all column collation in database !
> how can i do ?
> thank's
>
>|||yes it's my problem
i have make script to drop index
and alter procedure for all column have a collation but i have new problem !
i want to re create a index (drop before the alter)
how can i do, for scripting create index of tables ?
i know a table who have index dropped !
i search a script who waiting table in parameters and return
script to create index ? (before drop of course)
!
"craig_amtdatatechnologies@.discussions.mi"
< craigamtdatatechnologiesdiscussionsmi@.di
scussions.microsoft.com> a crit
dans le message de news:
7C701026-9D52-4DBE-A425-43109BE4D0C3@.microsoft.com...[vbcol=seagreen]
> Hi,
> If the problem is that the column has 1 type of collation specified for
> it,
> and you wish to change it (e.g. to the default collation for the
> database),
> then the following might help ...
>
> ALTER TABLE <tablename>
> ALTER COLUMN <name>
> <datatype> COLLATE <new collation e.g. Latin1_General_CI_AS>
> ... <other column options>
> GO
> Sources of information for this include 'Collations, changing' in SQL 2000
> BOL and 'How To: Set Column Collation' in SQL 2005 BOL
> Collation can be changed both through T-SQK or in SSMS
> Hope the above helps
> Craig
>
> "Ch." wrote:
>
Monday, March 19, 2012
Collapsing Three Rows Into One with T-SQL Challange?
Hello,
I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem
You will get nowhere until you normalize your tables!!!
Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)
=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus
Room
ID Room
1 101
2 102
3 201
4 202
Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F
TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20
ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================
BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:
ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA
CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]
Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||
donni100 wrote:
Do you think I could accamplish here?
I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.
Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.
If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||
Give this a shot.
--First dump the result set into the first temp table (#temp1)
CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))
SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.
DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1
OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time
set @.DayOfWeek = ''
--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end
insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room
Drop table #temp2
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
END
--cleraning up the cursor
CLOSE cur_room_time
DEALLOCATE cur_room_time
--selecting the final result set
Select *
from #temp3
DROP TABLE #temp3
Hope this works for you!
Ron N
|||It should work. Thanks a lot for help!|||i belive this can help
well use this function to get retrive the string that cotaians the rows of the specific ID.
CREATEFUNCTION dbo.ConRow(@.JID int)
RETURNSVARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output =COALESCE(@.Output+', ','')+CONVERT(varchar(20), JP.a)
FROM [E_JobPending] JP
WHERE JP.JobID = @.JID
RETURN @.Output
END
select dbo.ConRow(jobid), vE_Job.*
from
vE_Job
DROPFUNCTION dbo.ConRow
|||Untested, but should give a single row
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]
Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:
SELECT pt.Building, pt.Time, pt."Room #"
, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek
FROM (
SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"
, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq
FROM tbl as t
) AS t1
PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt
You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.
Collapsing Three Rows Into One with T-SQL Challange?
Hello,
I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem
You will get nowhere until you normalize your tables!!!
Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)
=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus
Room
ID Room
1 101
2 102
3 201
4 202
Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F
TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20
ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================
BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:
ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA
CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]
Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||
Do you think I could accamplish here?
I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.
Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.
If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||
Give this a shot.
--First dump the result set into the first temp table (#temp1)
CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))
SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.
DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1
OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time
set @.DayOfWeek = ''
--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end
insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room
Drop table #temp2
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
END
--cleraning up the cursor
CLOSE cur_room_time
DEALLOCATE cur_room_time
--selecting the final result set
Select *
from #temp3
DROP TABLE #temp3
Hope this works for you!
Ron N
|||It should work. Thanks a lot for help!|||i belive this can help
well use this function to get retrive the string that cotaians the rows of the specific ID.
CREATE FUNCTION dbo.ConRow(@.JID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)
FROM [E_JobPending] JP
WHERE JP.JobID = @.JID
RETURN @.Output
END
select dbo.ConRow(jobid), vE_Job.*
from
vE_Job
DROP FUNCTION dbo.ConRow
|||Untested, but should give a single row
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]
Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:
SELECT pt.Building, pt.Time, pt."Room #"
, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek
FROM (
SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"
, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq
FROM tbl as t
) AS t1
PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt
You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.
Collapsing Three Rows Into One with T-SQL Challange?
Hello,
I am wondering if someone has any good ideas how I could concatenate values in column 7:30- 9:50 so that I would have one row and a value: MTTHFW. Or even if it is possible having this in logical days of a week order : MTWTHF.
Thanks a lot for any help!
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 Wahhh the old Database Systems 101 "course/room/schedule" problem
You will get nowhere until you normalize your tables!!!
Course(ID,CourseName)
Room(ID, Room)
Day(No, Name, Abbrv)
TimeSlot(ID, DayNo, StartTime, Length) -Different Days May have different Time Allotments
ScheduledCourse(CourseID, RoomID, TimeSlotID)
=======================================
Sample Data
=======================================
Course
ID CourseName
1 Engineering
2 Biology
3 Calculus
Room
ID Room
1 101
2 102
3 201
4 202
Day
No Name Abbrv
1 Monday M
2 Tuesday T
3 Wednesday W
4 Thursday Th
5 Friday F
TimeSlot
ID DayNo StartTime Length NOTE: StateTime and Length are DateTimes!!
1 1 7:30 2:20
2 2 7:30 2:20
3 3 7:30 2:20
4 4 7:30 2:20
5 5 7:30 2:20
6 1 7:30 2:20
7 2 10:10 2:20
8 3 10:10 2:20
9 4 10:10 2:20
10 5 10:10 2:20
ScheduledCourse
CourseID RoomID TimeSlot
1 3 1
1 3 2
1 3 3
1 3 4
1 3 5
=======================================
Try it out . . .
=======================================
create table Course(ID int identity primary key,CourseName sysname)
create table Room(ID int identity primary key, Room sysname)
create table ClassDay(Number int , DayName sysname primary key, Abbrv sysname)
create table TimeSlot(ID int identity primary key, DayNo int, StartTime dateTime, Length dateTime)
create table ScheduledCourse(CourseID int, RoomID int, TimeSlotID int, primary key(CourseID,RoomID, TimeSlotID ))
insert into Course (CourseName) values('Engineering')
insert into Course (CourseName) values('Biology')
insert into Course (CourseName) values('Calculus')
insert into Room (Room) values('101')
insert into Room (Room) values('102')
insert into Room (Room) values('201')
insert into Room (Room) values('202')
insert into ClassDay values(1, 'Monday','M')
insert into ClassDay values(2, 'Tuesday','T')
insert into ClassDay values(3, 'Wednesday','W')
insert into ClassDay values(4, 'Thursday','Th')
insert into ClassDay values(5, 'Friday','F')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(1, '7:30', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(2, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(3, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(4, '10:10', '2:20')
insert into TimeSlot (DayNo, StartTime, Length ) values(5, '10:10', '2:20')
insert into ScheduledCourse values(1, 3, 1)
insert into ScheduledCourse values(1, 3, 2)
insert into ScheduledCourse values(1, 3, 3)
insert into ScheduledCourse values(1, 3, 4)
insert into ScheduledCourse values(1, 3, 5)
insert into ScheduledCourse values(2, 1, 1)
insert into ScheduledCourse values(2, 2, 2)
insert into ScheduledCourse values(3, 2, 3)
insert into ScheduledCourse values(3, 1, 4)
=======================================
Now try this query:
=======================================
SELECT c.CourseName, r.Room, t.StartTime, t.Length, d.Abbrv
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
ORDER BY d.Number, t.StartTime, c.CourseName
=======================================
Yields this:
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 F
=======================================
almost there.... create this function
=======================================
create function DaysOfWeek(@.courseId int, @.roomId int) returns sysname
as begin
declare @.abbrv sysname
declare @.dayno int
declare @.temp sysname
declare curs cursor for
select distinct abbrv, number from classday where number in
(SELECT dayno
FROM ScheduledCourse INNER JOIN TimeSlot
ON ScheduledCourse.TimeSlotID = TimeSlot.ID
inner join ClassDay on TimeSlot.DayNo = ClassDay.Number
where ScheduledCourse.courseId = @.courseId and
ScheduledCourse.RoomId = @.RoomId)
order by number
open curs
fetch next from curs into @.abbrv, @.dayno
while @.@.fetch_status = 0
begin
fetch next from curs into @.temp, @.dayno
if @.@.fetch_status = 0
set @.abbrv = @.abbrv+@.temp
end
close curs
deallocate curs
return @.abbrv
end
=======================================
almost there. . .
change the previous query to include the function. . .
=======================================
SELECT distinct c.CourseName, r.Room, t.StartTime, t.StartTime+ t.Length, dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yeilds this. . .
=======================================
Biology 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 M
Biology 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 T
Calculus 101 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 Th
Calculus 102 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 W
Engineering 201 1900-01-01 07:30:00.000 1900-01-01 02:20:00.000 MTWThF
=======================================
Almost there. . . can you feel it? hold on!!! we need to format the times!!!!
=======================================
SELECT distinct c.CourseName, r.Room, cast(DatePart(hh, t.StartTime) as sysname) +':'+ cast(DatePart(mi, t.StartTime) as sysname) + ' - ' +
cast(DatePart(hh, t.StartTime+ t.Length) as sysname) +':'+
cast(DatePart(mi,t.StartTime+ t.Length) as sysname) ,
dbo.DaysOfWeek(s.courseId, s.roomId )
FROM ScheduledCourse s INNER JOIN Course c ON
s.CourseID = c.ID INNER JOIN Room r ON
s.RoomID = r.ID INNER JOIN TimeSlot t ON
s.TimeSlotID = t.ID INNER JOIN ClassDay d ON
t.DayNo = d.Number
=======================================
Yields. . .
=======================================
Biology 101 7:30 - 9:50 M
Biology 102 7:30 - 9:50 T
Calculus 101 7:30 - 9:50 Th
Calculus 102 7:30 - 9:50 W
Engineering 201 7:30 - 9:50 MTWThF
=======================================
BOO-YAH!
|||Thank you very much Allen. It looks like a great design but unfortunately I don't dba right to change schema of a table I pulling my data from. The table schema looks this:
ID, CSM_ID, CSM_FAC_ID_NAME, CSM_START_TIME, CSM_END_TIME, CSM_BLDG, CSM_ROOM, CSM_CAPACITY, CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN, CSM_COURSE_ID, CSM_COURSE_SEC_MEETING_ID, CSM_TECH, CSM_TERM, TimeRuleID, ViolatesRules, FacultyConflict, IsFromImport, ModifiedBy, IsDeleted, CoursePlannerID, IsArranged, IsTBA
CSM_MON, CSM_TUE, CSM_WED, CSM_THU, CSM_FRI, CSM_SAT, CSM_SUN include Y if there is a class. Based on this I have a query that figures out if there is a class or not in the paricular time slot:
--
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT DISTINCT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
[Tech] = CASE ISNULL(CSM_TECH, '')
WHEN '' THEN ''
ELSE 'X' END,
CSM_CAPACITY AS [Capacity],
[7:30-9:50] = CONVERT( VARCHAR (25), CASE ISNULL(CSM_MON, '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(CSM_TUE, '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(CSM_WED, '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(CSM_THU, '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(CSM_FRI, '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(CSM_SAT, '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(CSM_SUN, '') WHEN '' THEN '' ELSE 'SU' END) FROM
COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
) AS A
ORDER BY A.[Room Number]
Since I may have several different classes for the particular time slot, I can get multiple rows. Looking at the example, instead of three rows, I would like to have one row that would conatenate values from [7:30- 9:50] column into one string. So I would have one row from Room#202 and a string MTTHFW. Do you think I could accamplish here?
Building Time Room # 7:30- 9:50
Engeneering 7:30:00 AM - 9:50:00 AM 201 MTTH
Engeneering 7:30:00 AM - 9:50:00 AM 201 F
Engeneering 7:30:00 AM - 9:50:00 AM 201 W|||
donni100 wrote:
Do you think I could accamplish here?
I don't think so . . . at least not in T-SQL without having rights to create a stored procedure / function.
Someone needs to grab the dba and have him redesign the database as the table is not in third normal form.
If a database isn't in (at least) third normal form, it makes doing things via sql extremely difficult.|||
Give this a shot.
--First dump the result set into the first temp table (#temp1)
CREATE TABLE #temp3 --Final result table
(Building varchar(30),
Time varchar(40),
[Room #] int,
[Day of week] varchar(10))
SET NOCOUNT on --don't want the row affected count displaying.
declare @.DayOfWeek varchar(10), @.Room varchar(20), @.Time varchar(40)
--Now we create the cursor get the distinct times and room numbers and well flip --threw them.
DECLARE cur_room_time CURSOR FOR
SELECT DISTINCT Room, Time
FROM #temp1
OPEN cur_room_time
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Creating the temp table that we will eveluate the day of the week
Select *
into #temp2
from #temp1
where Room = @.Room
and Time = @.Time
set @.DayOfWeek = ''
--Checking for Monday (M)
If exists(Select * from #temp2 where charindex('M', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek +'M'
end
--Checking for Tuesday (T) and insuring that it is not (TH)
If exists(Select * from #temp2 where charindex('T', DOW) > 0 and charindex('T', DOW)<> charindex('TH', DOW))
begin
Select @.DayOfWeek = @.DayOfWeek + 'T'
end
--Checking for Wednesday (W)
If exists(Select * from #temp2 where charindex('W', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'W'
end
--Checking for Thursday (TH)
If exists(Select * from #temp2 where charindex('TH', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'TH'
end
--Checking for Friday (F)
If exists(Select * from #temp2 where charindex('F', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'F'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SA', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SA'
end
--Checking for Saturday (SA)
If exists(Select * from #temp2 where charindex('SU', DOW)> 0)
begin
Select @.DayOfWeek = @.DayOfWeek + 'SU'
end
insert into #temp3
Select Building, Time, Room, @.DayOfWeek from #temp2 group by Building, Time, Room
Drop table #temp2
FETCH NEXT FROM cur_room_time
INTO @.Room, @.Time
END
--cleraning up the cursor
CLOSE cur_room_time
DEALLOCATE cur_room_time
--selecting the final result set
Select *
from #temp3
DROP TABLE #temp3
Hope this works for you!
Ron N
|||It should work. Thanks a lot for help!|||i belive this can help
well use this function to get retrive the string that cotaians the rows of the specific ID.
CREATE FUNCTION dbo.ConRow(@.JID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)
FROM [E_JobPending] JP
WHERE JP.JobID = @.JID
RETURN @.Output
END
select dbo.ConRow(jobid), vE_Job.*
from
vE_Job
DROP FUNCTION dbo.ConRow
|||Untested, but should give a single row
SELECT IDENTITY(int, 1,1) AS Custom_ID, A.* INTO #EarlyMorning FROM (
SELECT
'Bannan for 05FQ' AS Building,
'7:30-9:50' AS Time,
CSM_ROOM AS [Room Number],
CASE ISNULL(CSM_TECH, '') WHEN '' THEN '' ELSE 'X' END AS [Tech],
CSM_CAPACITY AS [Capacity],
CONVERT( VARCHAR (25), CASE ISNULL(MAX(CSM_MON), '') WHEN '' THEN '' ELSE 'M' END
+ CASE ISNULL(MAX(CSM_TUE), '') WHEN '' THEN '' ELSE 'T' END
+ CASE ISNULL(MAX(CSM_WED), '') WHEN '' THEN '' ELSE 'W' END
+ CASE ISNULL(MAX(CSM_THU), '') WHEN '' THEN '' ELSE 'TH' END
+ CASE ISNULL(MAX(CSM_FRI), '') WHEN '' THEN '' ELSE 'F' END
+ CASE ISNULL(MAX(CSM_SAT), '') WHEN '' THEN '' ELSE 'SA' END
+ CASE ISNULL(MAX(CSM_SUN), '') WHEN '' THEN '' ELSE 'SU' END) AS [7:30-9:50]
FROM COURSESCH_MEET
WHERE CSM_BLDG = 'ENG'
AND CSM_TERM = '05FQ'
AND CAST(CSM_START_TIME AS datetime) BETWEEN '7:30:00 AM' AND '9:50:00 AM'
AND CAST(CSM_END_TIME AS datetime)BETWEEN '7:30:00 AM' AND '9:50:00 AM'
GROUP BY CSM_ROOM,CSM_TECH,CSM_CAPACITY
) AS A
ORDER BY A.[Room Number]
Please post the version of SQL Server you are using so it is easier to suggest the correct solution. If you are using SQL Server 2005 you can use PIVOT operator and ROW_NUMBER in a query like below:
SELECT pt.Building, pt.Time, pt."Room #"
, pt.[1] + coalesce(pt.[2], '') + coalesce(pt.[3], '') + coalesce(pt.[4], '') as DaysOfWeek
FROM (
SELECT t.Building, t.Time, t."Room #", t."7:30- 9:50"
, ROW_NUMBER() OVER(PARTITION BY t.Building, t.Time, t."Room #" ORDER BY t."7:30- 9:50") as seq
FROM tbl as t
) AS t1
PIVOT (max(t1."7:30- 9:50") for t1.seq in ([1], [2], [3], [4] /*... as many maximum rows per grouping above*/)) as pt
You can do the same query above in older versions of SQL Server also. Use a temporary table to generate the sequence (possibly) or use correlated sub-query. And convert PIVOT to GROUP BY query with CASE expressions in SELECT list.
Collapsing groups while toggling intteractive sort
Hi all
i have a table with grouping by the first column.
the details section is hidden and can be expanded by the group box (with +).
i have interactive sort based on the second column.
While toggling the interactive sort button the expanded field being colappsed.
Is there any option for keeping the expanded rows in their state?
THank You
No, currently toggling interactive sort would reset the show/hide state. Changing this behavior is on our wish list for a future release.
Subscribe to:
Posts (Atom)