How can I prevent, or remove, empty strings from being added to this result set?

Question

0.00/5 (No votes)

See more:

I've got this code:

C#

internal static List<String> GetLocalities()
{
    List<PhotraxBaseData> psets = new List<PhotraxBaseData>();
    using (var db = new SQLite.SQLiteConnection(App.DBPath))
    {
        string sql = "SELECT DISTINCT City FROM PhotraxBaseData ORDER BY City";
        psets = db.Query<PhotraxBaseData>(sql);
    }
    List<String> cityNames = (from a in psets select a.City).ToList();
    return cityNames;
}

...which gives me more-or-less good data, except that I get an Empty String in the list, too. How can I ignore empty strings as "City" values so that they are not added to the cityNames list?

Posted 1-Nov-14 19:11pm

B. Clay Shannon

Add a Solution

3 solutions

Solution 1

change your sql statement

string sql = "SELECT DISTINCT City FROM PhotraxBaseData WHERE isnull(City,'') <> '' ORDER BY City";

Posted 1-Nov-14 19:01pm

DamithSL

Solution 2

C#

using Linq;

// sample test
private List<string> psets = new List<string>
{
    "",
    "London",
    "", 
    null, 
    " ", 
    "\t\t",
    "\n",
    "New York"
};


List<string> cityNames = psets.Where(
    str => 
    ! 
    (String.IsNullOrEmpty(str) 
    || 
    String.IsNullOrWhiteSpace(str)))
    .ToList();

// result => London / New York

Posted 1-Nov-14 20:39pm

BillWoodruff

Updated 1-Nov-14 20:41pm

v3

Comments

DamithSL 2-Nov-14 3:20am

psets.Where(s => ! String.IsNullOrWhiteSpace(s)).ToList()
will do the same

BillWoodruff 2-Nov-14 6:17am

Thanks, Damith, that's a good point; I don't know where I got in the habit of adding both checks. I've voted your SQL solution +5.

bool s = String.Empty == ""; // ==> true;

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

B. Clay Shannon · Accepted Answer · 2014-11-02T13:03:00

This, based >on Bill Woodruff's idea, works for me:

C#

internal static List<String> GetLocalities()
{
    List<PhotraxBaseData> psets = new List<PhotraxBaseData>();
    using (var db = new SQLite.SQLiteConnection(App.DBPath))
    {
        string sql = "SELECT DISTINCT City FROM PhotraxBaseData ORDER BY City";
        psets = db.Query<PhotraxBaseData>(sql);
    }
    List<String> cityNames = (from a in psets select a.City).Where(
        str =>
        !
        (String.IsNullOrEmpty(str)
        ||
        (String.IsNullOrWhiteSpace(str)))).ToList();
    return cityNames;
}