I just ran into a situation I have never known I was in before (I know be careful where you run). I was writing some SQL (one of my favorite things to do), and had a record not show up that I knew matched the criteria. I use a like operator in the query and in the table used to compare I have a wild card (%). This works great. Except for when the main table has a null.
The reason the record didn't show up was due to the fact that the table had a null cell. Since I was working in Oracle, it was very easy to fix this.
nvl(table1.column_name, ' ') like table2.column_name
That fixed it. I really prefer MS SQL Server over Oracle, but that is a fantastic little method/function.
So I figured this wouldn't be the same in SQL Server. Sure enough like doesn't work with a null cell. I'm not taking the time now to think about how to handle this in SQL Server, but I'm sure it's much longer.
Come to think about it I suppose this would work:
(table1.column_name like table2.column_name or table1.column_name is null)
So there you have it.
2 comments:
In SQL server there is an ISNULL function: ISNULL(LastName, '').
Thanks Richard. I knew SQL server had to be at least as smart as Oracle. I never encountered that when I was working heavily in SQL server. I do miss those days. It was so much more enjoyable than Oracle.
Post a Comment