Only put a period "." if there is a middle intial.
-
I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)
I need my resultant field data to look like the following:
"Doe, John P."
I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".
I have tried the following CASE statement:
CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END
However, I get an error indicating that the CASE statement is not supported in the Query Designer.
How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?
-
If you SET CONCAT_NULL_YIELDS_NULL ON (which I believe is the default value, you should be able to accomplish this very easily:
select LastName + ', ' + firstname + isnull(' ' + MiddleInit + '.', '')
from jxfa_person
-
That worked perfectly! Thanks 🙂
-
There is an current thread on this, where COALESCE() is used. Just for the sake of completeness this is more ANSI SQL 92 conform than the proprietary ISNULL().
Results are in both cases the same.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
As a side note, I've been asking MS for an ISNULL function in SQL-CE. I never thought of looking if COALESCE was part of the SQL-CE language since I always used ISNULL in my T-SQL.
This thread has opened my eyes
. COALESCE IS part of SQL-CE. Thanks.
Once you understand the BITs, all the pieces come together
-
Looking into this was very helpful for me. Coming to SQL Server from other platforms, I had assumed Null + ‘string’ = empty_string + ‘string’ = ‘string’. It seemed odd to me that coalesce() would work this way.
According to BOL, CONCAT_NULL_Yields_Null defaults to OFF. This would produce a ‘.’ where the middle initial is NULL.
It is ODBC and OLEDB which by default change the connection’s setting to ON. If you are counting on this behavior, you might want to set the property for the database (sp_dboption 'DBNAME', 'concat_null_yields_null', 'on').
Read BOL for this option though, there are index and view considerations (although they don’t look like they would affect you if you want it turned ON).
-Greg -
quote:
It is ODBC and OLEDB which by default change the connection’s setting to ON. If you are counting on this behavior, you might want to set the property for the database (sp_dboption 'DBNAME', 'concat_null_yields_null', 'on').
Good point. But it wouldn't hurt to just put SET CONCAT_NULL_YIELDS_NULL ON at the begining of your query.
-
jxflagg,
Does putting SET CONCAT_NULL_YIELDS_NULL ON at the begining of the query set the value for that query only? Or, will it need to be reset to its default value after the query is done?
-
AFAIK, you can set this option for a session which when set overwrites the same database option.
I would explicitely set it off.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
Does putting SET CONCAT_NULL_YIELDS_NULL ON at the begining of the query set the value for that query only? Or, will it need to be reset to its default value after the query is done?
If the set statement is inside a stored procedure, it is only for that procedure. If it's in a regular batch, it applies to the session.
. COALESCE IS part of SQL-CE. Thanks.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply