Using substring FOR XML PATH to concat data into record - need to remove Parens where no data found

Question

Post reply

Using substring FOR XML PATH to concat data into record - need to remove Parens where no data found

Mile Higher Than Sea Level

SSCarpal Tunnel

Points: 4828
More actions
June 15, 2015 at 8:34 am

#398591

Please disregard or delete this question.
I posted the source of the concat view so the question is easier to understand.
Sorry for the confusion.
The View in design mode: the FOR XML PATH takes a view with a column sorted by ID_Wells, a column with an Environmenatl Stip code, and a STIP with the dates column. The two dates (Mo/Day Start Mo/Day End) represent something like a breeding period for sensitive wildlife with a Stipulation that no noisy equipment or construction can take place during that time.
This view will only return a maximum of 21,000 records by the end of its lifecycle, it is a read-only table.
I don't want to see empty parens. NOTE: the parens are genereated in another view. - see follow up question - sorry for my confusion.
SELECT STIP2.[ID_Wells], substring
((SELECT ', ' + STIP1.[STIPwDate] AS [text()]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP1
WHERE STIP1.[ID_Wells] = STIP2.[ID_Wells]
ORDER BY STIP1.[ID_Wells] FOR XML PATH('')), 2, 1000) [STIPwDate]
FROM [RegulatoryDB].[dbo].[vRegulatory_Nav_GIS_Stips] STIP2
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 1

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?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

Put the optional parts inside the isnull...

For example

ISNULL(N'( ' + column1 + N' )',N'')

Which will produce a value with parentheses or nothing if the value is null.

If you want to ignore empty non-null strings as well

ISNULL(N'( ' + NULLIF(column1,N'') + N' )',N'')

Sorry, I didn't use your example code because of the mismatched parens...wasn't sure what you were trying to achieve, so thought a general example would help more.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);