Posts Tagged ‘ms sql’

Empty Strings, Nulls, and MS SQL

Wednesday, August 27th, 2008

I have a database that I am exporting data from. Once it is in text format I run the dos sort command, then finally compress it. Everything was going well till I noticed that my dump files had lines that were being split in two

I started looking at the original file and found it contained Ascii character 0 or Null. So my first response was to try isnull(column,’-‘) in the query, but that didn’t work

I checked the lookup tables and found that a join was matching and returning an empty string.

On reading the BCP manual I found this

out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.

So nulls are converted to empty strings and empty strings are converted to nulls? Who thought this was a good idea?

So I changed my sql to be NULLIF(column,”) and now everyone is happy.

MS SQL numeric to ip

Thursday, June 5th, 2008

I love the fact that I know SQL and can go against the database directly instead of having to accept someone elses preconceived ideas of what kind of data I need.

I needed to find out all hits in a database from a specific range of IP addresses range. the table stores the IP’s in a numeric form which is a no brainer on mysql you just use inet_ntoa and inet_atoa to convert back and forward, but Microsoft SQL Server (mssql) doesn’t support that so a little googling later I came up with this page, which has some stored procedures for doing the convertion, now I am just waiting for the database to come back with the data. SWEET