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:
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:

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