USE TEMPORARY
GO
IF OBJECT_ID('dbo.ReplaceCoalesce') IS NOT NULL
DROP PROCEDURE dbo.ReplaceCoalesce
GO
CREATE PROCEDURE dbo.ReplaceCoalesce @Input VARCHAR(MAX)
AS
SET NOCOUNT ON
DECLARE @SQL VARCHAR(MAX) ;
WITH s1
AS (SELECT LTRIM(RTRIM(Field)) AS Field
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@Input, ')', ' '), '(', ' '), '
', ','), ' ', ' '), ' ', ','), ',')
WHERE Field NOT IN ('CONVERT', 'VARCHAR', 'COALESCE', '', 'IS', 'NOT', 'NULL'))
SELECT @SQL = '(' + Util.dbo.StringConcat(DISTINCT Field + ' IS NOT NULL', '
OR ') + ')'
FROM s1
EXEC Util.dbo.PrintLargeText
@SQL
GO
--- SAMPLE CALL
EXEC TEMPORARY.dbo.ReplaceCoalesce
@Input = 'COALESCE(CONVERT(VARCHAR, t1.[TXPR_ADDR_APT_NBR]), CONVERT(VARCHAR, t1.[TXPR_ADDR_CARRT_CD]), CONVERT(VARCHAR, t1.[TXPR_ADDR_CITY_NM]),
CONVERT(VARCHAR, t1.[TXPR_ADDR_CNTRY_NM]), CONVERT(VARCHAR, t1.[TXPR_ADDR_CSZ_ID]), CONVERT(VARCHAR, t1.[TXPR_ADDR_DIR_CD]),
CONVERT(VARCHAR, t1.[TXPR_ADDR_HSE1_NBR]), CONVERT(VARCHAR, t1.[TXPR_ADDR_HSE2_NBR]), CONVERT(VARCHAR, t1.[TXPR_ADDR_MODE_CD]),
CONVERT(VARCHAR, t1.[TXPR_ADDR_QDRNT_CD]), CONVERT(VARCHAR, t1.[TXPR_ADDR_SFX1_CD]), CONVERT(VARCHAR, t1.[TXPR_ADDR_ST_CD]),
CONVERT(VARCHAR, t1.[TXPR_ADDR_STRT_NM]), CONVERT(VARCHAR, t1.[TXPR_ADDR_ZIP_CD]), CONVERT(VARCHAR, t1.[TXPR_CENS_ID]),
CONVERT(VARCHAR, t1.[TXPR_CENTROID_CD]), CONVERT(VARCHAR, t1.[TXPR_DPID_CD]), CONVERT(VARCHAR, t1.[TXPR_ETAL_IND]),
CONVERT(VARCHAR, t1.[TXPR_LAT_DEGR]), CONVERT(VARCHAR, t1.[TXPR_LONG_DEGR]), CONVERT(VARCHAR, t1.[TXPR_MATCH_CD]),
CONVERT(VARCHAR, t1.[TXPR_STD_HSE1NBR]), CONVERT(VARCHAR, t1.[TXPR_STD_HSE2NBR]), CONVERT(VARCHAR, t2.[StdAddr1]),
CONVERT(VARCHAR, t2.[StdAddr2]), CONVERT(VARCHAR, t2.[StdAddr3]), CONVERT(VARCHAR, t2.[StdAddr4]), CONVERT(VARCHAR, [T3].[OrigAddr1]),
CONVERT(VARCHAR, [T3].[OrigAddr2]), CONVERT(VARCHAR, [T3].[OrigAddr3]), CONVERT(VARCHAR, [T3].[OrigAddr4]), NULL) IS NOT NULL'