Questions tagged [empty-string]
14 questions
122
votes
11 answers
When to use NULL and when to use an empty string?
I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general:
Is there a logical scenario in which it would be useful to distinguish an empty string from NULL?
What would be the physical storage implications for…
Maniero
- 2,758
- 6
- 28
- 29
19
votes
1 answer
Empty Strings: Why or when is '' equal to ' '?
Who can explain why
select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' ');
yields
----------- ----------- ----------- ----------- -----------
1 0 0 0 1
The funny…
bernd_k
- 12,369
- 24
- 79
- 111
10
votes
4 answers
Oracle empty string converts to null
When doing an insert, an empty string is converted to null:
insert into test (f) values ('');
Now, there is a row with f containing a null.
But, when I query the table, I cannot use '':
select * from test where f='';
no rows selected
I can use…
kainaw
- 1,404
- 3
- 15
- 26
6
votes
3 answers
Empty LONGTEXT or NULL?
In my MySQL DB I have one field called html_contents that contains all the html to be shown in a webpage. Obviously the html could be huge, and certaintly bigger than 64KB, therefore I decided to use LONGTEXT rather than VARCHAR.
When the field is…
Marco Demaio
- 161
- 1
- 1
- 6
5
votes
1 answer
Query performance of a NULL vs an empty ('') varchar in SQL Server 2012
I have 3 indexed fields in a query: int, int, and varchar(250).
The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty of empty string varchar values. Queries with the…
Kevin
- 323
- 4
- 8
4
votes
4 answers
Should I convert empty varchar values to NULL?
I have a stored procedure which accepts various varchar parameters. The middle tier code which calls the procedure has not been consistent in terms of the values it submits. For example, sometimes a parameter like "Transport Description" will be…
8kb
- 2,639
- 2
- 32
- 36
4
votes
2 answers
Remove nulls from TSQL connection string
I have a bunch of queries, against a couple different databases, that I use to generate delimited reports. I'm trying to create some new reports against a MSSQL database and I'm having trouble replicating our other processes to get consistent…
WernerCD
- 1,245
- 3
- 11
- 19
3
votes
3 answers
Questions about handling NULLs and empty strings in nvarchar and numeric fields
I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I basically have to treat NULLs, empty strings and (pure)…
Anonymous Maximus
- 779
- 2
- 8
- 16
3
votes
2 answers
COALESCE with cast
I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get:
SQL Error [22P02]: ERROR: invalid input syntax…
user101289
- 269
- 6
- 16
1
vote
1 answer
Simple query not returning blank records
Very simple example here to a very frustrating treasure hunt for the mystery character.
I have a table with a varchar(1) NOT NULL column - everything default - ANSI_PADDING & NULLS are ON for the table as well. We'll call this column CodeNum
A…
Matt Laffoon
- 13
- 3
1
vote
1 answer
Empty string values - PostgreSQL 9.2
I've got some empty string values being putting into my DB.
Code I've used:
CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$
SELECT
CASE
WHEN length($1::VARCHAR) = 0 THEN NULL
ELSE
…
user83914
0
votes
0 answers
How to specify default CREATE TABLE values just once?
Is there any way I can just say once that the default type should be TEXT, and the
default value should be '' for all CREATE TABLEs?
Currently I must do:
CREATE TABLE "t" (dbm TEXT DEFAULT '', rsrp TEXT DEFAULT '', cdma TEXT DEFAULT ''...
(I'm…
Dan Jacobson
- 101
0
votes
1 answer
Inconsistent ResultSet from Procedure with Variables
I'm working on building a procedure with two inputs, and the Syntax checks out, but the result-set is not consistent with the logic.
In this question, there are in actual fact two questions. Number 1 is the most important in this context. Number 2…
Johan Brink
- 111
- 3
0
votes
0 answers
How to pass array of UUIDs *OR* empty array and return implicit (inner join)?
How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty?
This question (Casting an array of texts to an array of UUIDs) asks for the answer I am looking for, but the…
user3.1415927
- 101
- 1
- 1