and x is NULL <> and x = 'NULL'

Question

and x is NULL <> and x = 'NULL'

sqldriver

SSChampion

Points: 10440
More actions
August 22, 2013 at 1:26 pm

#278938

Just when I thought this was about the silliest thing I'd seen...
I had someone using NULL as a string in an Excel file that I imported, which made some count queries go b-a-n-a-n-a-s.
Some days I think those Amish are on to something.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

You'd be suprised what you can find these days 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sqldriver SSChampion Points: 10440 More actions · Answer 2

Koen Verbeeck (8/22/2013)
You'd be suprised what you can find these days 🙂

In the same file something was doing MATH on a phone number column during import.

So 212-555-5555

or 212-555-5555 / 212-555-5556

Were being treated as expressions. They were saved as General format in Excel. When I changed the format to Text, they went in fine.

aaron.reese SSChampion Points: 13498 More actions · Answer 3

Love it 😛

Reminds me of one of my favourite jokes:

Q: Why does Excel get Christmas and Halloween confused?

A: OCT31 = DEC25 (actually OCT2DEC(31) = 25 but it doesn't sound so good when you write it like that.

The general rule with Excel always seems to be don't trust it unless Excel created it in the first place.

I have a CSV file I need to bring into SQL occasionally and it contains product codes. The two which always confuse Excel are "012" which it sees as 12 (Apparently it knows better than me that numbers don't start with a leading zero so it kindly removes them to save me the trouble) and bizarrely "2E2" which it assumes I meant two Exponent two so saves as 200 2E2 couldn't possibly be a string, it is a valid numerical notation even though the other 3000 cells in the column are strings.

sqldriver SSChampion Points: 10440 More actions · Answer 4

aaron.reese (8/22/2013)
Love it 😛
Reminds me of one of my favourite jokes:
Q: Why does Excel get Christmas and Halloween confused?
A: OCT31 = DEC25 (actually OCT2DEC(31) = 25 but it doesn't sound so good when you write it like that.
The general rule with Excel always seems to be don't trust it unless Excel created it in the first place.
I have a CSV file I need to bring into SQL occasionally and it contains product codes. The two which always confuse Excel are "012" which it sees as 12 (Apparently it knows better than me that numbers don't start with a leading zero so it kindly removes them to save me the trouble) and bizarrely "2E2" which it assumes I meant two Exponent two so saves as 200 2E2 couldn't possibly be a string, it is a valid numerical notation even though the other 3000 cells in the column are strings.

It does that a lot with zip codes for me. It also does a bang up job of converting any foreign number that starts with 0 into exponential notation, from where there is no return. It's especially fond of doing that to UK numbers, for some reason.

Many people, including my boss, seem to think that this will fix it:

update table set columntofix= convert(numeric(38,0),cast(columntofix as float))

But it usually just gives me a close but no cigar number that has the last three digits rounded up. So, if a phone number ends in 9679, it will be 9700.

mickyT SSChampion Points: 10360 More actions · Answer 5

I personally loathe the one where something like

7/21

gets converted to

Jul-21

Dwain Camps SSC Guru Points: 86908 More actions · Answer 6

I hate it when OPENROWSET imports a value of ' (empty string in the Excel cell) as an empty string and thus thinks there's extra rows in the input set.

Have to teach people how to properly delete rows of spreadsheets you import from.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sqldriver SSChampion Points: 10440 More actions · Answer 7

dwain.c (8/22/2013)
I hate it when OPENROWSET imports a value of ' (empty string in the Excel cell) as an empty string and thus thinks there's extra rows in the input set.
Have to teach people how to properly delete rows of spreadsheets you import from.

I work with one client who puts a copyright notice at the end of his data pulls, so the six lines of that and 2 blank rows on either end of it get brought in.

And an insurance company with a large elderly subscriber base in Western PA. So elderly that there is sometimes a 00 for their birth month or day, which causes all sorts of arithmetic errors when trying to calculate current age. I have no idea why or how this is allowed.