I think you are trying to live in Joe Celko's dream world where you can only use standard SQL and in any given week you may have to port all of your code from SQL Server to Oracle and then Oracle to DB2 and then back to SQL Server again. Twice.
While the core and fundamental aspects of standard SQL will help you anywhere, trying to limit yourself to that set of the language for fear of future porting (or just on principle) is not a path I'd recommend. Personally, I stick to ANSI standard stuff when I can (e.g. <> vs. !=, COALESCE vs. ISNULL, CURRENT_TIMESTAMP vs. GETDATE(), etc.), but I'm also not afraid to use the SQL Server-specific stuff that makes my work easier.
It is important to understand how SQL works in general; it is equally important to understand how the language works in your RDBMS(es) - including deviations from the standard, deviations from the way another RDBMS might have implemented the same concept, and proprietary extensions that don't exist elsewhere.
SQL Server, as an example, covers a good portion of the standard, and it gets closer to full compliance with each new version. Will it ever cover 100%? Highly doubtful. Will it continue to add proprietary extensions not in the standard? Certainly. If everyone covered the standard and nobody stepped outside of it, then there would be no advantages to choosing one platform over another, and we'd all be using the same thing.
The wiscorp.com page SQL Standards has several older revisions and drafts of SQL:20nn (zip):
The 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 has at page 289:
If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value.
Best Answer
The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree.
While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This almost always means using DBMS-specific versions of SQL and DBMS-specific features.
Your employer/client has paid good money for a license for their DBMS, and often gone through a selection process to do so. Not using the DBMS's features is a waste of money.
I like to say it's like buying a Ferrari and always sticking to the speed limit...
On the other hand, if you're just learning SQL, and T-SQL just happens to be the dialect you have at your disposal, then, yes, try to learn standard SQL as your knowledge will be more broadly applicable. But don't discount learning your particular DBMS's features if you are in a commercial (or non-academic) environment.