pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...
> What if I can't?
> "gracie" wrote:
>> I have a situation where the primary key is:
>> user_id char(20) not null
>> user_cd char(2) not null
>> allows users to store trailing spaces, which in turn makes the key not unique.
>> For example it allows the following, one with spaces one without:
>> ernie 01
>> ernie 01
>> This in turn causes processing problems in the app.
>> How do I fix this problem? BTW, ANSI_PADDING is turned off.
>> Thanks,
>> Gracie|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
> What if I can't?
> "gracie" wrote:
> > I have a situation where the primary key is:
> >
> > user_id char(20) not null
> > user_cd char(2) not null
> >
> > allows users to store trailing spaces, which in turn makes the key not unique.
> >
> > For example it allows the following, one with spaces one without:
> >
> > ernie 01
> > ernie 01
> >
> > This in turn causes processing problems in the app.
> >
> > How do I fix this problem? BTW, ANSI_PADDING is turned off.
> >
> > Thanks,
> > Gracie|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:
>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

pk & trailing spaces

I have a situation where the primary key is:
user_id char(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not uniqu
e.
For example it allows the following, one with spaces one without:
ernie 01
ernie 01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
GracieUse VARCHAR not CHAR.
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||What if I can't?
"gracie" wrote:

> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not uni
que.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie|||Char is a fixed length datatype, it will always pad spaces to length of data
declaration with
spaces. Did you mean varchar?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
>|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
>|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:

>I have a situation where the primary key is:
>user_id char(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not uniq
ue.
>For example it allows the following, one with spaces one without:
>ernie 01
>ernie 01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

pk & trailing spaces

I have a situation where the primary key is:
user_idchar(20) not null
user_cd char(2) not null
allows users to store trailing spaces, which in turn makes the key not unique.
For example it allows the following, one with spaces one without:
ernie01
ernie01
This in turn causes processing problems in the app.
How do I fix this problem? BTW, ANSI_PADDING is turned off.
Thanks,
Gracie
Use VARCHAR not CHAR.
http://www.aspfaq.com/
(Reverse address to reply.)
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
> I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie
|||What if I can't?
"gracie" wrote:

> I have a situation where the primary key is:
> user_idchar(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not unique.
> For example it allows the following, one with spaces one without:
> ernie01
> ernie01
> This in turn causes processing problems in the app.
> How do I fix this problem? BTW, ANSI_PADDING is turned off.
> Thanks,
> Gracie
|||Char is a fixed length datatype, it will always pad spaces to length of data declaration with
spaces. Did you mean varchar?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1CDF624A-3879-4BD9-81F1-01A4043E17D7@.microsoft.com...[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
|||Is it by design that you allow users to put trailing spaces? If not make a
small change in the insert statement on the web page sql statement...
RTRIM(user_id)
Am suggesting this only if you can't change the datatype to varchar.
Thanks
GYK
"gracie" wrote:
[vbcol=seagreen]
> What if I can't?
> "gracie" wrote:
|||"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:DC0DFC0D-1017-40CD-A5C3-C6BD70224E62@.microsoft.com...
>I have a situation where the primary key is:
> user_id char(20) not null
> user_cd char(2) not null
> allows users to store trailing spaces, which in turn makes the key not
> unique.
> For example it allows the following, one with spaces one without:
> ernie 01
> ernie 01
>
I don't understand.
For char(20)
'ernie'
and
'ernie '
are stored the same, as
'ernie '
and are considered duplicates.
ANSI_PADDING has no effect on non-nullable char columns. ANSI_PADDING
controls whether trailing blanks are trimmed from varchar columns, and
whether nullable char columns are padded to width before being stored (this
is because nullable char columns are actually stored as as varchars).
But in general, I agree with what others have said: don't use for user_id,
use varchar. CHAR is ok for fixed-width coded like user_cd, but for data
which actually varies in length, varchar is better.
David
|||Gracie,
I can't reproduce the behavior you describe. Regardless of the data types
or collations on the columns, ('ernie','01') and ('ernie ','01') will
be considered
duplicate keys, since ('ernie' = 'ernie ') and ('01' = '01') is true.
Trailing spaces
are ignored in string comparisons for all string data types and for all
collations,
as far as I know.
Steve Kass
Drew University
gracie wrote:

>I have a situation where the primary key is:
>user_idchar(20) not null
>user_cd char(2) not null
>allows users to store trailing spaces, which in turn makes the key not unique.
>For example it allows the following, one with spaces one without:
>ernie01
>ernie01
>This in turn causes processing problems in the app.
>How do I fix this problem? BTW, ANSI_PADDING is turned off.
>Thanks,
>Gracie
>

Pivot Question

I want to pivot some data returned from a query. I've use the standard
technique [Sum(Case...)] below in the past, but in this situation it's not
ideal.
Typical solution:
SELECT
SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2002
In my case, I'm listing the Parts that need despatching and how many are
needed of each. The first issue is that there are 30-50 parts typically, and
I don't think that 30-50 Sum(Case...) statements are the best solution.
Secondly (and more importantly), new parts are added and removed on a
regular basis, so hard-coding like this is pure folly.
What are the best alternatives to this? Are there any?
I have a way around this problem using my ASP code, but I'm interested in a
general solution for such a scenario for future purposes as well.
Thanks
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Am I missing something.
Grouping on part name or number seems the obvious solution.|||Am I missing something.
Grouping on part name or number seems the obvious solution.|||"andy" <aon14@.lycos.co.uk> wrote in message
news:1134388670.211751.172940@.o13g2000cwo.googlegroups.com...
> Am I missing something.
Either of us could be...

> Grouping on part name or number seems the obvious solution.
>
Yeah that gets me so far...
PartNo - Count:
Part1 - 10
Part2 - 20
Part3 - 15
Whereas I want:
Part1 - Part2 - Part3
10 - 20 - 15
This is a bit of a contrived example since the solution is easy enough to
achieve in my ASP code, but I've had examples in the past where the ASP
solution wasnt appropriate...
CJM|||The best solution is the one you've already cited; do it on the client
side. Your server will love you for it :)
However, if you must do it on the server side, google for "SQL Server
dynamic pivot" for various solutions to this problem. Most have
limitations, but they can do the trick. As an aside, SQL Server 2005
has a PIVOT command built-in.|||Hi,
Why not try a third party product
AGS Crosstab Builder for SQL 2000
www.ag-software.com
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
>I want to pivot some data returned from a query. I've use the standard
>technique [Sum(Case...)] below in the past, but in this situation it's not
>ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
> and I don't think that 30-50 Sum(Case...) statements are the best
> solution. Secondly (and more importantly), new parts are added and removed
> on a regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
> a general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||There is no way to dynamically create a pivot (even in 2k5 iirc)
Unless you goto the extremes and create dynamic sql statements and use
EXEC( ... ) - which is generally very hard to maintain and has performance
impacts.
Your best bet is to return the data to the client as a two field dataset and
cross tab it in the front end.
Part Number
-- --
KSTX 1345
QUZR 45
38XJ 8723
9MSU 1437
SM5J 127
MABV 731
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:OR6%23VEx$FHA.2784@.tk2msftngp13.phx.gbl...
> I want to pivot some data returned from a query. I've use the standard
> technique [Sum(Case...)] below in the past, but in this situation it's not
> ideal.
> Typical solution:
> SELECT
> SUM(CASE WHEN MONTH(OrderDate) = 1 THEN 1 END) AS 'January'
> ,SUM(CASE WHEN MONTH(OrderDate) = 2 THEN 1 END) AS 'February'
> ,SUM(CASE WHEN MONTH(OrderDate) = 3 THEN 1 END) AS 'March'
> ,SUM(CASE WHEN MONTH(OrderDate) = 4 THEN 1 END) AS 'April'
> ,SUM(CASE WHEN MONTH(OrderDate) = 5 THEN 1 END) AS 'May'
> ,SUM(CASE WHEN MONTH(OrderDate) = 6 THEN 1 END) AS 'June'
> ,SUM(CASE WHEN MONTH(OrderDate) = 7 THEN 1 END) AS 'July'
> ,SUM(CASE WHEN MONTH(OrderDate) = 8 THEN 1 END) AS 'August'
> ,SUM(CASE WHEN MONTH(OrderDate) = 9 THEN 1 END) AS 'September'
> ,SUM(CASE WHEN MONTH(OrderDate) = 10 THEN 1 END) AS 'October'
> ,SUM(CASE WHEN MONTH(OrderDate) = 11 THEN 1 END) AS 'November'
> ,SUM(CASE WHEN MONTH(OrderDate) = 12 THEN 1 END) AS 'December'
> FROM Sales.SalesOrderHeader
> WHERE YEAR(OrderDate) = 2002
> In my case, I'm listing the Parts that need despatching and how many are
> needed of each. The first issue is that there are 30-50 parts typically,
and
> I don't think that 30-50 Sum(Case...) statements are the best solution.
> Secondly (and more importantly), new parts are added and removed on a
> regular basis, so hard-coding like this is pure folly.
> What are the best alternatives to this? Are there any?
> I have a way around this problem using my ASP code, but I'm interested in
a
> general solution for such a scenario for future purposes as well.
> Thanks
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> However, if you must do it on the server side, google for "SQL Server
> dynamic pivot" for various solutions to this problem. Most have
> limitations, but they can do the trick. As an aside, SQL Server 2005
> has a PIVOT command built-in.
>
One thing hampering my search via google is the proliferation of help on SQL
2005!
Still... should be migrating across soon...|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1134389360.359667.322330@.g14g2000cwa.googlegroups.com...
> The best solution is the one you've already cited; do it on the client
> side. Your server will love you for it :)
> ... SQL Server 2005 has a PIVOT command built-in.
She loves me...she loves me not...:)
For dynamic xtabs and other type of pivoting problems on the server side
check out RAC @.
www.rac4sql.net