Help with SQL concatenation Query 5

[dibblm]

select cfname + ' ' + (isnull(cmi , ' cmi ')) + '. ' + clname as expr1

This works and solves my null value issue I started with however I get periods on records that have no mi.

I would like to delete the fact of inserting a period if there is no MI.
example:

John A. Doe
Mary B.
Jane Doe (with no period for no MI)

 

[drctx]

would this work:

select cfname + ' ' + (isnull(cmi , ' cmi' + '.')) + ' ' + clname as expr1

 

[BobJacksonNCI]

How about:

select cfname + ' ' + iif(isnull(cmi), '', "cmi + '. '&quot + clname as expr1

You might have to play with the quotation marks...

 

[Norris68]

There is no SQL equivalent of the Access IIF function, and I'm pretty sure you would not be able to use it in an ADP. It's such a useful function it seems a major ommision from SQL Server.

The only alternative would be to write a SQL UDF. Another problem with this is that you cannot pass a boolean evaluation as a parameter. The closest I got was to create a series of UDFs called IIF_EQ, IIF_GT, IIF_GTE, etc. Here is my definition of the equality check (IIF_EQ) - it uses sql_variant parameters so you can use any data types.

CREATE FUNCTION dbo.[IIF_EQ] (@FirstValue as sql_variant, @SecondValue as sql_variant, @ValueIfTrue as sql_variant, @ValueIfFalse as sql_variant)
RETURNS sql_variant
AS
BEGIN
DECLARE @Result AS sql_variant
IF @FirstValue=@SecondValue SET @Result = @ValueIfTrue ELSE SET @Result = @ValueIfFalse
RETURN @Result
END

So using the UDF, you would want...

SELECT cfname + ' ' + CONVERT(varchar(2),dbo.IIF_EQ(cmi,Null,'',cmi + '.')) + ' ' + clname AS Expr1

In my experience with ADPs, you need the dbo. You need the CONVERT, as unfortunately you cannot concatenate a sql_variant even if it is a string.

 

[dibblm]

This message would be to Norris.

I think your method is going to work best for me. However more complex but getting the job done.

I have never created a UDF this is the first. I basically tried just copying your code and using what you handed me. SQL errors out wanting the variants defined. Hoever they look defined in the beginning as variants.

DO I have to declare every variant before using them somewhere?

 

[Norris68]

What version of SQL are you using? The sql_variant data type was introduced in SQL 2000, as were UDFs.

Never mind! I have done without using UDFs.

SELECT cfname + ' ' + ISNULL(NULLIF(cmi+'. ','. '),'') + clname AS Expr1

 

[dibblm]

I can't seem to reply to you directly so I am posting back to this group.

I am using SQL 2000. However would still like to know how you came across your other method. I dont like always taking the long way out and am the type to want to know how things work inside and out.

I am presuming the UDP is a function ? (User Defined Function?)

 

[dibblm]

Something else I wnated to add.

whenever I create a query like the above. Sql is adding a 'N' character...

what is this? Is this just so SQL knows that it is a nvarchar?

 

[Norris68]

Correct - a User Defined Function. I created it via Enterprise Manager. If you do follow this route, don't forget to assign permissions so your users can use the function. I suppose you could probably do similar using a stored procedure, but the UDF seems made for the job - very useful when you can't quite manipulate the standard functions enough in your query.

 

[Norris68]

From SQL BOL, "Using Unicode Data" from "Accessing and Changing Relational Data" section:

Unicode constants are specified with a leading N: N'A Unicode string'

I must admit I had wondered about that, but never bothered looking it up before.

 

[BobJacksonNCI]

I shoulda knowed better (IIF) - I'm working on an ADP now...

Note: ISNULL(NULLIF(cmi+'. ','. '),'')

doesn't work with SQL 7.

Bob

 

[dibblm]

Thank you all for your support. Alot has been learned here today. I think this posting may probably want to be put forth as a helpfull or expert post due to the fact that it has answered many question.

The unicode expression as well the iif (nullif) expressions and concatenation as well.

PS all beware who run an NT based platform the blaster worm. Please patch all systems and help to be part of the prevention of viruses.

 

[cmmrfrds]

The CASE statement in sql server 2000 can replace the iif function in Access.

SELECT last_name, first_name, middle_name,
case
when middle_name is null then
last_name + isnull(middle_name,' ') + first_name
else
last_name + ' ' + isnull(middle_name,' ') + '.' + first_name
end as fullname
from providers

 

[Norris68]

BobJacksonWCom: It should work on SQL 6.5 - it worked on SQL 7, although it trimmed the space after the full stop (unlike SQL 2000 which keeps it in place). Have you tried it in query analyser using a constant string instead of the fields?

SELECT 'Norris' + ' ' + ISNULL(NULLIF('6'+'. ','. '),'') + '8'
SELECT 'Norris' + ' ' + ISNULL(NULLIF(Null+'. ','. '),'') + '8'

First should return 'Norris 6. 8' in SQL2K, 'Norris 6.8' in SQL7.
Second should return 'Norris 8' in both versions.

dibblm: You nominate posts you consider helpful/expert by clicking the 'Mark this post as a helpful/expert post!' link under the posting Glad it's working for you now.

 

[BobJacksonNCI]

Norris68 - Thanks!

I don't have access to SQL query analyzer (pun intended)
I'd have to kick my boss off his computer...

Strictly working with Access Project.

This thread if of interest because I work with middle names that are sometimes null. Handled in plain Access, still perfecting in Access Project.

KUTGW (Keep Up The Good Work!)
Bob
P.S. Star earned in part for problem resolution and in part for hanging in there until all questions are answered.

 

[Norris68]

Shucks, thanks!

 

[BobJacksonNCI]

Hopefully this post will cause the thread to pop up on Norris68's monitor ... Or anyone else who can assist.

I thought I was done with this, but am still wrestling with an ADP (AC2000 / SQL 7) Stored Procedure. I'd like to include the middle initial in an extracted name field, and I'm having difficulty. Note that I do not have access to SQL, only the front end. NULLIF is not in any online help I have and I cannot find it in Microsoft Knowledgebase.

Existing Stored Procedure code follows, which results in a blank NAME when there is no middle initial.

dbo.MgrTB_tbl_Missed_Punch.EmpLastName + ', ' + dbo.MgrTB_tbl_Missed_Punch.EmpLglFirstName
+ ' ' + dbo.MgrTB_tbl_Missed_Punch.EmpMidName AS NAME

Note that unlike the originator of this thread, I'm placing the middle initial at the end. Thus, the code suggested for him/her is not what I'm looking for.

Please provide syntax for NULLIF. Ideally, an example, too.

Thank YOU in advance!

 

[Norris68]

SQL Server books online is on the MSDN website at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp

NULLIF documentation is here:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_nos-nz_3uhy.asp?frame=true

For you stored procedure you will want:
dbo.MgrTB_tbl_Missed_Punch.EmpLastName + ', ' + dbo.MgrTB_tbl_Missed_Punch.EmpLglFirstName
+ ISNULL(NULLIF(' '+dbo.MgrTB_tbl_Missed_Punch.EmpMidName,' '),'') AS NAME

If a space + the initial is a space, the NULLIF function will return Null, otherwise it will return the space + initial. If the result is Null, then ISNULL function will return an empty string instead, otherwise it will return the output from the NULLIF function. If you are still getting problems, you may need to put an extra ISNULL around the initial field to convert it to an empty string.

...+ ISNULL(NULLIF(' '+ISNULL(dbo.MgrTB_tbl_Missed_Punch.EmpMidName,''),' '),'') AS NAME

BTW: I'd avoid using 'NAME' as the alias, as it is a reserved word!

 

[BobJacksonNCI]

Your assistance is MUCH appreciated!

 

[neecie]

I'm getting confused following ISNULL NULLIF ISNULL!
Doesn't the following do the same?

dbo.MgrTB_tbl_Missed_Punch.EmpLastName + ', ' + dbo.MgrTB_tbl_Missed_Punch.EmpLglFirstName
+ ' ' +

 ISNULL(dbo.MgrTB_tbl_Missed_Punch.EmpMidName,'')

AS NAME

As Case statement would better deal with the extra space (if it is an issue)

dbo.MgrTB_tbl_Missed_Punch.EmpLastName + ', ' + dbo.MgrTB_tbl_Missed_Punch.EmpLglFirstName
+

 CASE LEN(dbo.MgrTB_tbl_Missed_Punch.EmpMidName) WHEN > 0 THEN ' ' + dbo.MgrTB_tbl_Missed_Punch.EmpMidName END

AS NAME

Personally, I prefer CASE statements. They do work in Access 2000(+)/ SQL 7(+). When creating a view, you have to input the sql text manually in the SQL pane. The diagram and grid panes won't function but the query will.
Ignore any errors referencing the CASE statement when saving