SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
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.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27275 Visits: 13268
You'd be suprised what you can find these days :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
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
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1039 Visits: 902
Love it :-P

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
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
aaron.reese (8/22/2013)
Love it :-P

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
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1574 Visits: 3317
I personally loathe the one where something like
7/21
gets converted to
Jul-21
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7249 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
sqldriver
sqldriver
SSC Eights!
SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)SSC Eights! (972 reputation)

Group: General Forum Members
Points: 972 Visits: 2518
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search