CODEWITHSUNDEEP
postgresql
Hello lad
Hello lad

Reputation: 18790

postgresql empty string has length 1

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

Upvotes: 0

Views: 381

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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.

Upvotes: 0

user330315
user330315

Reputation:

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'))

Upvotes: 1

Related Questions