Empty Char in String not registering as empty

Viewed 33

I've been scratching my head for a few hours on this one, and I've exhausted why this might be happening.

I'm importing data from Excel and staging it to SQL to perform cleanups. The Price field type in Excel is Accounting. Values >= 1000 is saved like this: 1 000; 10 000 etc.

The whitespace between the digits does not appear to be recognized as any common 'nothingness', for the lack of a better term.

I've tried pulling this out in SQL and my application code and it's not replacing the empty space with nothing in these instances.

Here's 'some' of what I've tried:

SQL:

SELECT
price,
space             = IIF(CHARINDEX(CHAR(32), price) > 0, 1, 0),
horizontal_tab    = IIF(CHARINDEX(CHAR(9), price) > 0, 1, 0),
vertical_tab      = IIF(CHARINDEX(CHAR(11), price) > 0, 1, 0),
backspace         = IIF(CHARINDEX(CHAR(8), price) > 0, 1, 0),
carriage_return   = IIF(CHARINDEX(CHAR(13), price) > 0, 1, 0),
newline           = IIF(CHARINDEX(CHAR(10), price) > 0, 1, 0),
formfeed          = IIF(CHARINDEX(CHAR(12), price) > 0, 1, 0),
nonbreakingspace  = IIF(CHARINDEX(CHAR(255), price) > 0, 1, 0)
FROM QuoteStagingDataSep2022;

As you can see, no recognition of whitespace between the zero and three in the highlighted red block...

Result:

enter image description here

C#:

This is just my test case to see if I could clean these values up in C# because I wasn't able to in SQL, no matter what approach I took.

private async void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            StringBuilder price = new StringBuilder();
            dt = await logic.QueryQuoteStagingData();
            for (int i = 7; i < dt.Rows.Count; i++)
            {
                foreach (char item in dt.Rows[i]["Price"].ToString())
                {
                    if (string.IsNullOrEmpty(item.ToString()))
                        item.ToString().Replace(" ", "");

                    price.Append(item.ToString());
                }
            }
        }

Here's an image depicting that there is actually an empty character in this position, but the debugger skips the if statement: enter image description here

I can longer understand why this is not seen as nothing. I'm out of ideas...

2 Answers

An empty string and a string consisting of just whitespace aren't the same thing.

An empty string is simply "", which is the value of string.Empty.

You need to use string.IsNullOrWhiteSpace(myString) in your check.

After @jhmckimm's insight, I realized it was a commonly used character replacement i.e. CHAR(9), CHAR(10), CHAR(13) etc.

It was actually CHAR(160).

All sorted now :)

Related