How to Remove Empty Strings

Question

I'm trying to find a way to trim or remove spaces in what appeared to be empty strings so that when I look for a min value I get a result with characters and not an empty string with spaces.

Originally I tried excluding empty fields ( !="" ) however since the empty strings contain spaces its technically not an empty string so I tried Trimming and still cant get the result set that I need.

select trim(Field_1) as Field_1
from Table_1
where Field_Date between '2019-08-01' and '2019-08-31'
and Field_1 != ""
order by Field_1 asc

The Result Set that I get is:

Blank (6 Spaces)
Blank (6 Spaces)
Blank (6 Spaces)
ABC
DEF
GHI

The Result Set that I Need

ABC
DEF
GHI

harpocrates · Accepted Answer · 2019-09-04T14:28:22.600

1

It looks like your WHERE condition is not being trimmed whereas your SELECT is.

In your case you are using Field_1 as the reference in your condition, which appears to refer to the table's column and not the alias you created by saying trim(Field_1) as Field_1

If you only want results that have no whitespace there is no need to trim() your selected column since your condition enforces that your result can have no whitespace, so why not use trim() in your second condition?

That way every record's value for Field_1 will be trimmed first and then compared to "".

How about something like this?

select Field_1
from Table_1
where Field_Date between '2019-08-01' and '2019-08-31'
and trim(Field_1) != ""
order by Field_1 asc

edited Sep 04 '19 at 14:28

answered Sep 04 '19 at 14:19

harpocrates

140
7

Great, that works. Thank you so much for the solution as well as the explanation. – ace123 Sep 04 '19 at 14:43
@ace123 Glad to hear that! – harpocrates Sep 04 '19 at 14:44

GhastlyCode · Answer 2 · 2019-09-04T14:50:13.187

0

Personally I'd be looking at why you're getting empty strings in the first place. If they're supposed to be empty record sets then you've certainly got some code inserting unnecessary spaces somewhere.

Fixing this issue would stop any blank statements from appearing in the SQL search as they would come back as null. This would also save you in future as blank spaces in a database can be quite a pain!

However, if you're still looking for a way to replace the spaces in your code I'd suggest reading this article: REPLACE Multiple Spaces with one

I believe this will put you on the right track.

edited Sep 04 '19 at 14:50

answered Sep 04 '19 at 14:18

GhastlyCode

248
1
11

Good Point. Thank you for that – ace123 Sep 04 '19 at 14:34
No problem, I've edited the answer to provide a link to replace spaces which I believe may help you for your current issue. – GhastlyCode Sep 04 '19 at 14:51

alexherm · Answer 3 · 2019-09-04T14:45:05.040

0

select Field_1
from Table_1
where Field_Date between '2019-08-01' and '2019-08-31'
and Field_1 not like '% %'
order by Field_1 asc

edited Sep 04 '19 at 14:45

answered Sep 04 '19 at 14:35

alexherm

1,362
2
18
31

How to Remove Empty Strings

3 Answers3