Check if database table string field is empty or NULL??

[Ian Branch 121]

Hi team,

Given a database table field   statement..

MyTable.Fieldbyname('MyField').AsString

What is the best/most efficient way to test if it is empty or NULL??

IsNullOrEmpty(MyTable.Fieldbyname('MyField').AsString)

But Delphi didn't like it.

 

Regards & TIA,

Ian

[Stano 140]

MyTable.Fieldbyname('MyField').IsNull

Edited December 24, 2022 by Stano

[Ian Branch 121]

Hi Stano,

Doesn't that assume that the field is Null if empty?

 

Ian

[Attila Kovacs 617]

3 hours ago, Ian Branch said:

What is the best/most efficient way to test if it is empty or NULL??

if it's a string field then you can check against empty string => .AsString = ''   , both, empty string and NULL will be true

(side note: you can set a field to NULL with ".Clear", if it's a nullable field)

 

[programmerdelphi2k 235]

note in SQL databases:

• CHAR( n ) = space is add on field to fill it  <-- FIXED TYPO! not "VAR(n)"
• VARCHAR( n ) = no-space is add on field to fill it

sometimes needs a "TRIM( ... )" to remove all spaces if none-char exists (if not automatic done by Delphi)

Edited December 24, 2022 by programmerdelphi2k

[programmerdelphi2k 235]

var
  MyVar: string;
begin
  if ''.IsNullOrEmpty(MyVar) then //   Result := Value = Empty;
  or
  if ''.IsNullOrWhiteSpace(MyVar) then //   Result := Value.Trim.Length = 0;
    caption := 'null or empty'
  else
    caption := 'no-no-no';

 

Edited December 24, 2022 by programmerdelphi2k

[Attila Kovacs 617]

2 hours ago, programmerdelphi2k said:

note in SQL databases:

• VAR( n ) = space is add on field to fill it 

what the actual f VAR(n) is?

[programmerdelphi2k 235]

sorry typo! not "VAR(n)"  but yeah "CHAR(n)"  

[Attila Kovacs 617]

1 minute ago, programmerdelphi2k said:

sorry typo! not "VAR(n)"  but yeah "CHAR(n)"  

Then it's a lie.

[programmerdelphi2k 235]

maybe.. you can read Firebird documentation if dont!

 

https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.CHAR  a long text to reflect

https://www.firebirdfaq.org/faq237/

Quote

What's the difference between CHAR and VARCHAR?

 

The most important difference is that CHAR is padded with spaces and VARCHAR is not. For example, if you have:

• CREATE TABLE t1 ( c1 VARCHAR(2),  c2 CHAR(2)  );
• INSERT INTO t1 (c1,c2) VALUES ('a', 'a');

 

The column c1 will contain value 'a', while column c2 will contain value 'a ' with additional space.

Trailing spaces are ignored when doing comparisons, so both columns would match the WHERE c = 'a' clause of some query. Trailing spaces are respected by LIKE operator, which is a source of confusion for beginners (example in FAQ #309).

 

Edited December 24, 2022 by programmerdelphi2k

[Attila Kovacs 617]

that is only FB

[programmerdelphi2k 235]

maybe... you can verify yourself!

[Remy Lebeau 1264]

13 hours ago, Ian Branch said:

What is the best/most efficient way to test if it is empty or NULL??

IsNullOrEmpty(MyTable.Fieldbyname('MyField').AsString)

But Delphi didn't like it.

IsNullOrEmpty() is a method of TStringHelper, so you have to call it on a string instance, eg:

if MyTable.FieldByName('MyField').AsString.IsNullOrEmpty then

Which is really no better than simply testing for an empty string (since AsString can't return a null string):

if MyTable.FieldByName('MyField').AsString = '' then

However, since Delphi strings don't differentiate between null and empty (they are both a nil pointer), if you really need to differentiate then you will have to use TField.IsNull instead, eg:

if MyTable.FieldByName('MyField').IsNull then
  // is null ...
else if MyTable.FieldByName('MyField').AsString = '' then
  // is empty ...
else
  // is not null or empty ...