For XML Path - to Concatenate - need to remove empty parens

[Rx_]

The single record view uses this:
ISNULL(dbo.APD_Stips1.Stip_Abv, N' ') + N' ' + N'(' + ISNULL(dbo.APD_Stips2.Dt_Start, N' ') + N' ' + ISNULL(dbo.APD_Stips2.Dt_End, N' ' + N')') + N')'

SQL Server 2008r2

Most of the three fields have a Stips Code, a begin period, a end period.
In most cases the code above returns something like this: BO (03/01 08/15)
In the case where there is a code, but the Begin period and End period are empty, it returns one of the two formats.
Actual example: mostly this PM ( ) and sometimes this CL ( ))

The desired output would be this: PM

In MSACCESS or a later version of SQL the IF statement would make this easy.
How would this be done in SQL Server 2008r2?

My first preference is to get rid of any () that have no dates and leave the (5/10 6/30) ones there.

 

[Rx_]

Upon further Examination:
the code above calls the single records and concats them. The single record view is this:
ISNULL(dbo.APD_Stips1.Stip_Abv, N' ') + N' ' + N'(' + ISNULL(dbo.APD_Stips2.Dt_Start, N' ') + N' ' + ISNULL(dbo.APD_Stips2.Dt_End, N' ' + N')') + N')'

SQL Server 2008r2
Two exact copies of empty Parens are: PM ( ) and CL ( ))

Would enclosing the entire field with a Replace ( ) with empty string be a possible solution?

REPLACE ( string_expression , string_pattern , string_replacement )
How would one include both ( ) and ( )) to be replaced with an empty character?

 

[Rx_]

Worked on refining the question and got this answer on SQL Server Central.
This works just great.
But... I discovered another issue with the SQL Server Native Client 11.0 for SQL Server Linked tables.

MS Access shows all of my empty fields (Start date / End date) as NULL.
It turns out these are not really dates since they are reoccurring periods.
They are Character. The reason the random Parens shows up is that on the SQL server side, the LEN function shows there are empty quotes (or empty character) in the field. On MS Access Linked table side, they display as null.
So, in the random cases where a Date field had a empty character, the T-SQL ISNULL is false instead of true.
This was not an issue on the MS Access side. It became evident on the SQL Server View side.

Here is a working solution that creates a SampleData table then runs the query against it.

CREATE TABLE dbo.SampleData(
Stip_Abv nvarchar(10),
Dt_Start nvarchar(10),
Dt_End nvarchar(10))
INSERT INTO SampleData
VALUES( 'PM', '05/15', '06/20'),
( 'PM', NULL, '06/20'),
( 'PM', '05/15', NULL),
( 'PM', NULL, NULL)

SELECT ISNULL(Stip_Abv + N' ', N' ') + ISNULL( N'(' + Dt_Start + N' ' + Dt_End + N')', N''),
ISNULL(Stip_Abv + N' ', N' ') + ISNULL( N'(' + NULLIF( ISNULL( Dt_Start, N'N/A') + N' ' + ISNULL( Dt_End, N'N/A'), N'N/A N/A') + N')', N'')
FROM SampleData;

DROP TABLE dbo.SampleData;

 

[BlueIshDan]

Rx_ Normally I would dive face first into solving a problem with you, but I'm having a numb brain week!

 

[Rx_]

I was waiting and waiting for you, didn't go home for three days than gave up.
So, I started with out you this time.

This was just one of the fires that had to be put out so I can leave for NYC (New York City, not that Old York City across the pond).
My son is graduating from the www.usmma.edu
A Federal Service Academy takes three days of pompus ceremony to graduate someone.
I bring him home for six days. Then he starts his job as a licensed 3rd class union maritime engineer with General Dynimics.
First two months, he works on a harbor on Long Island just across from NYC. To be 22, working in in NY.

But next time, you better get right on it.

 

[BlueIshDan]

Holy moly! You have a smart boy! He's not too far from where I live. I'm just on the other side of main in New Brunswick, Canada

 

[Rx_]

Will keep that in mind. Didn't look, but assume there is a maritime harbor there.
As a ship engineer with 300 days of union certified work, just turned 22 last month, his license is unlimited horse power, any kind of power, anywhere in the world. He has additional licnese on the largest air-refirgration, plumbing, wielding, environmental, and I forget the rest.
Later he will take a three week course to be certified for an engineer on any private yatch.
We don't have any harbors here in Denver, Colorado altitude 5,280 feet.

once he goes to sea on merchant maritime assignments, he is more likely to visit your area.
To just apply, it takes a federal elected representative letter of Nomination. He scored in the top 5% of national SAT/ACT exams including math.
At the USMMA, he was only 'average'. But, what do you call the Harvard Medical School graduate with the lowest grade point average? You and I call him "Doctor". I don't care that he was only average. Damn, there are some really, really smart midshipmen there.
The Secretary of Transportation will be handing out the diplomas.
Yeah, I am one very lucky dad.