Remove UNION at the end of a String

Question

I have created some Dynamic SQL that is in a Variable @SQLQuery1. The word "UNION" is at the very end because my DynamicSQL is sweeping through databases and doing UNIONs along the way. So the last UNION is dangling by itself at the end of the string.

I know I can use RIGHT and CHARINDEX to remove the "UNION" at the very end...just looking for Syntax help...

Thanks in Advance for your help

Answer 1

If “UNION” is always present at the end, and there are no subsequent spaces, then:

set @SQLQuery1 = substring(@SQLQuery1, 1, len(@SQLQuery1) - len('UNION'))

Answer 2

SELECT @SQLQuery2 = SUBSTRING (@SQLQuery2, 1, LEN (@SQLQuery2) - CHARINDEX ('U', REVERSE (@SQLQuery2)))

;

Answer 3

Insert the statements you want to create for each database into a temp table with two columns, db and stmt. Then create the total query with:

SELECT @query = string_agg(convert(nvarchar(MAX), stmt), 'UNION ALL ' + char(13) + char(10))
FROM   #temp

Requires SQL 2017 or later.

And, yeah, UNION ALL. I don't think you want to remove duplicates from the result set, do you?

Answer 4

If you cannot make sure if the word "UNION" is always at the end of the string, you can try this:

DECLARE @SQLQuery1 varchar(max);
SET @SQLQuery1 = 'SELECT ID, Name FROM DB1.dbo.MyTable UNION SELECT ID, Name FROM DB2.dbo.MyTable UNION SELECT ID, Name FROM DB3.dbo.MyTable UNION';
SELECT CASE WHEN LEFT(REVERSE(@SQLQuery1), 5) = REVERSE('UNION') THEN REVERSE(SUBSTRING(REVERSE(@SQLQuery1), 6, LEN(@SQLQuery1) - 5)) ELSE @SQLQuery1 END;

Answer 5

Hi @Bobby P

Or you can try this.

Declare @SQLQuery2 varchar(max) = 'select * from table1 UNION select * from table2 UNION';
SELECT @SQLQuery2 =  left(@SQLQuery2,len(@SQLQuery2) - len('UNION'));
print @SQLQuery2;

Printout:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.