Home > mailing lists

Thread: Error in SELECT clause with UPPER function

Error in SELECT clause with UPPER function

From

"Pascal Rempenault"

Date:

15 November 2002, 14:35:50

1) Using

PostgreSQL 7.2.3 on i586-pc-linux-gnu, compiled by GCC 2.95.3

2) Description

track=# select loginname, upper(loginname) from tperson;
loginname |   upper
------------+------------
admin      | ADMIN
prempena   | PREMPENA
(2 rows)

track=# select loginname, upper(loginname) from tperson where upper(loginname)='ADMIN';
loginname | upper
-----------+-------
(0 rows)

3) Expected result

loginname | upper
------------+------------
admin | ADMIN
(1 row)

If someone can help to understand why …

Thanks in advance.

Pascal

Re: Error in SELECT clause with UPPER function

From

Tom Lane

Date:

15 November 2002, 20:18:43

"Pascal Rempenault" <prempenault@hubwoo.com> writes:
> track=# select loginname, upper(loginname) from tperson where
> upper(loginname)='ADMIN';
>  loginname | upper
> -----------+-------
> (0 rows)

I'm guessing that there are trailing blanks in loginname (possibly
because it's declared char(n) rather than varchar(n)).  The result
of upper() is always of type text, so comparison to it treats the
trailing blanks as significant.

            regards, tom lane

Re: Error in SELECT clause with UPPER function

From

"Lee Crampton"

Date:

16 November 2002, 17:19:10

Sorry, can't help with 7.2.3 but it does work with 7.2.1 in similar
circumstances:

                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

psql avbrief -c "select userid,upper(userid) from users where
userid='sdclee'"
 userid | upper
--------+--------
 sdclee | SDCLEE
(1 row)

psql avbrief -c "select userid,upper(userid) from users where
upper(userid)='SDCLEE'"

 userid | upper
--------+--------
 sdclee | SDCLEE
(1 row)

However, looking at your output, it looks like 'ADMIN' may be 'ADMIN   ', so
check that out.
To prove it, how about :

> track=# select loginname, upper(loginname) from tperson where
> upper(loginname)  like 'ADMIN%';

Hope that helps
Lee Crampton

""Pascal Rempenault"" <prempenault@hubwoo.com> wrote in message
news:NEBBIINNELNNLHDBPCCIMEAADLAA.prempenault@hubwoo.com...
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0017_01C28A4A.994F01A0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: 8bit
>
> 1) Using
> PostgreSQL 7.2.3 on i586-pc-linux-gnu, compiled by GCC 2.95.3
>
>
> 2) Description
>
> track=# select loginname, upper(loginname) from tperson;
>  loginname  |   upper
> ------------+------------
>  admin      | ADMIN
>  prempena   | PREMPENA
> (2 rows)
>
> track=# select loginname, upper(loginname) from tperson where
> upper(loginname)='ADMIN';
>  loginname | upper
> -----------+-------
> (0 rows)
>
>
> 3) Expected result
>  loginname  |   upper
> ------------+------------
>  admin      | ADMIN
> (1 row)
>
> If someone can help to understand why .
>
> Thanks in advance.
>
> Pascal
>
> ------=_NextPart_000_0017_01C28A4A.994F01A0
> Content-Type: text/html;
> charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable
>
> <html xmlns:o=3D"urn:schemas-microsoft-com:office:office"
xmlns:w=3D"urn:sc=
> hemas-microsoft-com:office:word"
xmlns=3D"http://www.w3.org/TR/REC-html40">
>
> <head>
> <meta http-equiv=3DContent-Type content=3D"text/html;
charset=3Diso-8859-1">
> <meta name=3DProgId content=3DWord.Document>
> <meta name=3DGenerator content=3D"Microsoft Word 9">
> <meta name=3DOriginator content=3D"Microsoft Word 9">
> <link rel=3DFile-List href=3D"cid:filelist.xml@01C28A4A.95D56B40">
> <!--[if gte mso 9]><xml>
>  <o:OfficeDocumentSettings>
>   <o:DoNotRelyOnCSS/>
>  </o:OfficeDocumentSettings>
> </xml><![endif]--><!--[if gte mso 9]><xml>
>  <w:WordDocument>
>   <w:View>Normal</w:View>
>   <w:Zoom>0</w:Zoom>
>   <w:DocumentKind>DocumentEmail</w:DocumentKind>
>   <w:HyphenationZone>21</w:HyphenationZone>
>   <w:EnvelopeVis/>
>  </w:WordDocument>
> </xml><![endif]-->
> <style>
> <!--
>  /* Font Definitions */
> @font-face
> {font-family:Tahoma;
> panose-1:2 11 6 4 3 5 4 4 2 4;
> mso-font-charset:0;
> mso-generic-font-family:swiss;
> mso-font-pitch:variable;
> mso-font-signature:16792199 0 0 0 65791 0;}
>  /* Style Definitions */
> p.MsoNormal, li.MsoNormal, div.MsoNormal
> {mso-style-parent:"";
> margin:0cm;
> margin-bottom:.0001pt;
> mso-pagination:widow-orphan;
> font-size:12.0pt;
> font-family:"Times New Roman";
> mso-fareast-font-family:"Times New Roman";}
> p.MsoAutoSig, li.MsoAutoSig, div.MsoAutoSig
> {margin:0cm;
> margin-bottom:.0001pt;
> mso-pagination:widow-orphan;
> font-size:12.0pt;
> font-family:"Times New Roman";
> mso-fareast-font-family:"Times New Roman";}
> span.StyleCourrierlectronique15
> {mso-style-type:personal-compose;
> mso-ansi-font-size:10.0pt;
> mso-ascii-font-family:Arial;
> mso-hansi-font-family:Arial;
> mso-bidi-font-family:Arial;
> color:black;}
> @page Section1
> {size:595.3pt 841.9pt;
> margin:70.85pt 70.85pt 70.85pt 70.85pt;
> mso-header-margin:35.4pt;
> mso-footer-margin:35.4pt;
> mso-paper-source:0;}
> div.Section1
> {page:Section1;}
> -->
> </style>
> </head>
>
> <body lang=3DFR style=3D'tab-interval:35.4pt'>
>
> <div class=3DSection1>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>1) Using  </span></font><font size=3D1
color=
> =3Dblack
> face=3DTahoma><span lang=3DEN-GB
style=3D'font-size:8.5pt;font-family:Tahom=
> a;
> color:black;mso-ansi-language:EN-GB'><o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>PostgreSQL 7.2.3 on i586-pc-linux-gnu, compiled
by=
>  GCC
> 2.95.3</span></font><font size=3D1 color=3Dblack face=3DTahoma><span lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'><o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'> <o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'><![if
!supportEmptyParas]> <![endif]><o:p></o=
> :p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>2) Description<o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'><![if
!supportEmptyParas]> <![endif]><o:p></o:p></span></font></=
> p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>track=3D# select loginname, upper(loginname) from
> tperson;<br>
>  loginname  |   upper<br>
> ------------+------------<br>
>  admin      | ADMIN<br>
>  prempena   | PREMPENA<br>
> (2 rows)</span></font><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'><o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'> <o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>track=3D# select loginname, upper(loginname) from
> tperson where upper(loginname)=3D'ADMIN';<br>
>  loginname | upper<br>
> -----------+-------<br>
> (0 rows)<o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'><![if
!supportEmptyParas]> <![endif]><o:p></o=
> :p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'><![if
!supportEmptyParas]> <![endif]><o:p></o=
> :p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>3) Expected result</span></font><font size=3D1
> color=3Dblack face=3DTahoma><span lang=3DEN-GB
style=3D'font-size:8.5pt;fon=
> t-family:
> Tahoma;color:black;mso-ansi-language:EN-GB'><o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'> loginname  |   upper<br>
> ------------+------------<br>
>  admin      | ADMIN<br>
> (1 row)</span></font><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'><o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'> <o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>If someone can help to understand why
…<o:p>=
> </o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'><![if
!supportEmptyParas]> <![endif]><o:p></o=
> :p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier
New";color:blac=
> k;
> mso-ansi-language:EN-GB'>Thanks in advance.</span></font><font size=3D1
> color=3Dblack face=3DTahoma><span lang=3DEN-GB
style=3D'font-size:8.5pt;fon=
> t-family:
> Tahoma;color:black;mso-ansi-language:EN-GB'><o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D1 color=3Dblack face=3DTahoma><span
lang=
> =3DEN-GB
>
style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=
> N-GB'> <o:p></o:p></span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 color=3Dblack face=3D"Courier
New"><span
> style=3D'font-size:10.0pt;font-family:"Courier
New";color:black'>Pascal</sp=
> an></font><span
> class=3DStyleCourrierlectronique15><font size=3D2 color=3Dblack
face=3DAria=
> l><span
>
style=3D'font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><o:p=
> ></o:p></span></font></span></p>
>
> </div>
>
> </body>
>
> </html>
>
> ------=_NextPart_000_0017_01C28A4A.994F01A0--
>