postgresql empty string has length 1

Question

I have a column type text which looks like empty string but the length of it is 1.

the following sql

select 
teilnetz_name, 
length(teilnetz_name), 
trim(teilnetz_name), 
length(trim(teilnetz_name))
from test_table

results in

teilnetz_name| length| btrim| length
             |  1    |      | 1

and

select case when trim(teilnetz_name) is distinct from '' then true else false end 
from test_table

--return true

select case when teilnetz_name is distinct from null then true else false end
from test_table

--return true

select case when teilnetz_name is distinct from '' then true else false end
from test_table

--return true

select case when teilnetz_name is distinct from ' ' then true else false end
from test_table

--return true

How can I explain this phenomenon ?

I have on postgreql version 12.3

user330315 · Accepted Answer · 2020-11-02 12:30:28Z

1

The column probably contains some other whitespace, e.g. a tab character. trim() will only remove real spaces.

Try

length(regexp_replace(teilnetz_name, '\s+', '', 'g'))

answered Nov 2, 2020 at 12:30

user330315

Sign up to request clarification or add additional context in comments.

Comments

Stefanov.sm · Accepted Answer · 2020-11-02 12:32:21Z

0

There may be a non-printable unicode character in field teilnetz_name. Try

select encode(teilnetz_name, 'hex') from test_table;

in order to see what teilnetz_name actually contains.

answered Nov 2, 2020 at 12:32

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Collectives™ on Stack Overflow

postgresql empty string has length 1

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related