chr(3) and 3::text

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

chr(3) and 3::text

Vladimir Kokovic-2

Hi,

Why is chr(3) is different than 3::text ?

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 28, 2020


Reply | Threaded
Open this post in threaded view
|

RE: chr(3) and 3::text

opa6x57

 

From: gmail Vladimir Koković <[hidden email]>
Sent: Saturday, March 28, 2020 6:22 AM
To: [hidden email]
Subject: chr(3) and 3::text

 

Hi,

Why is chr(3) is different than 3::text ?

 

[Ken Benson] Chr(3) – means return the character that has the ASCII code of “3” – which is a control character.

https://w3resource.com/PostgreSQL/chr-function.php

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 28, 2020



 

 

Ken Benson | ken @ infowerks-dot-com

 

 

Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

Vladimir Kokovic-2

OK,


And what is 3::text ?


On 28.3.20. 14:35, Ken Benson wrote:

 

From: gmail Vladimir Koković [hidden email]
Sent: Saturday, March 28, 2020 6:22 AM
To: [hidden email]
Subject: chr(3) and 3::text

 

Hi,

Why is chr(3) is different than 3::text ?

 

[Ken Benson] Chr(3) – means return the character that has the ASCII code of “3” – which is a control character.

https://w3resource.com/PostgreSQL/chr-function.php

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 28, 2020



 

 

Ken Benson | ken @ infowerks-dot-com

 

 

Reply | Threaded
Open this post in threaded view
|

RE: chr(3) and 3::text

opa6x57

From: gmail Vladimir Koković <[hidden email]>
Sent: Saturday, March 28, 2020 6:39 AM
To: Ken Benson <[hidden email]>; [hidden email]
Subject: Re: chr(3) and 3::text

 

OK,

 

And what is 3::text ?

 

[Ken Benson]  that means convert the numeric value “3” to a text data type … similar to CAST(NumericValue as text) in MSSQL

 

On 28.3.20. 14:35, Ken Benson wrote:

 

From: gmail Vladimir Koković [hidden email]
Sent: Saturday, March 28, 2020 6:22 AM
To: [hidden email]
Subject: chr(3) and 3::text

 

Hi,

Why is chr(3) is different than 3::text ?

 

[Ken Benson] Chr(3) – means return the character that has the ASCII code of “3” – which is a control character.

https://w3resource.com/PostgreSQL/chr-function.php

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

Vladimir Kokovic, DP senior (69)
Serbia, Belgrade, March 28, 2020




 

 

Ken Benson | ken @ infowerks-dot-com

 

 

Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

David G Johnston
In reply to this post by Vladimir Kokovic-2
On Sat, Mar 28, 2020 at 6:21 AM gmail Vladimir Koković <[hidden email]> wrote:

Hi,

Why is chr(3) is different than 3::text ?


First place to start given two things you may or may not know what they are is the index in the documentation:

From there:
Definiton of chr()


Meaning of "::"


Knowing that your data is likely in UTF but that for lower numbers code points (below 256) it doesn't actually matter as it is standard ASCII assumes some level of knowledge too.

Examples:

replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG


You seem to know that "Fillter" is text so neither of these seems particularly useful.  If you want to replace the string '3' in Filler with the empty string just write: replace(filler, '3', ''').

David J.
Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

Bzzzz
In reply to this post by opa6x57
On Sat, 28 Mar 2020 14:39:09 +0000
Ken Benson <[hidden email]> wrote:

From my console, simply "3".

Jean-Yves

> And what is 3::text ?



Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

David G Johnston
In reply to this post by Vladimir Kokovic-2
On Sat, Mar 28, 2020 at 6:21 AM gmail Vladimir Koković <[hidden email]> wrote:


replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

"Wrong" is not a helpful term unless you describe what you are expecting and how the result you got is different.  In particular just reading what you've typed the "wrong" one fails because you forgot the comma between "Fillter" and "3::text".  But I suspect you had a different unsaid expectation here.

In the future you should consider writing self-contained expressions, ideally ones that don't use physical tables if the problem permits.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

Vladimir Kokovic-2

David,

Sorry for copy/paste error:replace(Filler,3::text,'') WRONG !

Character 0x03 is valid in ASCII and UTF-8 character sets.

Main question is, why is 3::text == '3' ?

On 28.3.20. 16:04, David G. Johnston wrote:
On Sat, Mar 28, 2020 at 6:21 AM gmail Vladimir Koković <[hidden email]> wrote:


replace(Filler,chr(3),'') WORKS

replace(Filler3::text,'') WRONG

"Wrong" is not a helpful term unless you describe what you are expecting and how the result you got is different.  In particular just reading what you've typed the "wrong" one fails because you forgot the comma between "Fillter" and "3::text".  But I suspect you had a different unsaid expectation here.

In the future you should consider writing self-contained expressions, ideally ones that don't use physical tables if the problem permits.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

David G Johnston
On Sat, Mar 28, 2020 at 8:09 AM gmail Vladimir Koković <[hidden email]> wrote:

David,

Sorry for copy/paste error:replace(Filler,3::text,'') WRONG !

Character 0x03 is valid in ASCII and UTF-8 character sets.

Main question is, why is 3::text == '3' ?

The convention here is to bottom or inline post, trimming unnecessary content.

Its that way because :: is defined to make it that way. 
 
3 has a type of integer
'3' has a type of text
::<type> is the PostgreSQL type casting operator that casts its left to to <type> (see the documentation I linked to)

So:

integer::text == text

cast(integer as text) == text

is the SQL Standard equivalent expression

David J.

Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

Vladimir Kokovic-2
On 28.3.20. 16:25, David G. Johnston wrote:

The convention here is to bottom or inline post, trimming unnecessary content.

Its that way because :: is defined to make it that way. 
 
3 has a type of integer
'3' has a type of text
::<type> is the PostgreSQL type casting operator that casts its left to to <type> (see the documentation I linked to)

So:

integer::text == text

cast(integer as text) == text

is the SQL Standard equivalent expression

David J.

integer::text == text, but which character 0x03 or 0x33 ?

Text should contain characters.

Characters is from character set.

Character 0x03 is valid in ASCII and UTF-8 character sets.


Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

Tom Lane-2
In reply to this post by David G Johnston
"David G. Johnston" <[hidden email]> writes:
> On Sat, Mar 28, 2020 at 8:09 AM gmail Vladimir Koković <
> [hidden email]> wrote:
>> Main question is, why is 3::text == '3' ?

> Its that way because :: is defined to make it that way.

More concretely: for most types, casting to text is defined to produce the
textual representation of the value.  An unadorned literal 3 starts life
as type integer, so what you've got here is an integer-to-text cast,
and that's going to convert the integer the same way it would be output.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: chr(3) and 3::text

David G Johnston
In reply to this post by Vladimir Kokovic-2
On Sat, Mar 28, 2020 at 8:39 AM gmail Vladimir Koković <[hidden email]> wrote:
On 28.3.20. 16:25, David G. Johnston wrote:

integer::text == text, but which character 0x03 or 0x33 ?

x33

Text should contain characters.

Characters is from character set.

Character 0x03 is valid in ASCII and UTF-8 character sets.

So is x33

Which one is more useful to the typical programmer (which given you concept of works and wrong in the OP your problem space is probably not typical)?  Treating the integer value 3 as a code point reference or treating it as a character in a given character set.

I hope you agree that the character interpretation is more useful, though regardless of your agreement that is what happens.  Everything else is just an implementation detail.

Numbers > 9 are converted digit-by-digit into their character representations and then concatenated together.


David J.