HOW TO REPLACE A WORD FROM THE END OF T SQL STRING

I am running a while loop in my sql query
and creating a sql string, which I am executing at the end.

I am adding Union All in the sql server inside the while loop.

now I want to remove the last Union all from the sql string before running it otherwise I am getting an error becasue after last Union all there is no sql statements because the while loop ends.

Can any one help me out here, how can I replace the last union all from my sql string.
Mahesh YadavAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nativCommented:
The straight forward way is to substring the results
Set @Str = Substring(@str, 1, len(@str)- 9)


Another way is to make your While more clever not to add the last "Union All", but I cannot help without the code.
nativCommented:
By the way.
Set @Str = left(@str,len(@str)-9)
will give you the same results if you prefer.
Mahesh YadavAuthor Commented:
here is my complete code

DECLARE @PropertyProfitField VARCHAR(255), @STRSQL NVARCHAR(4000)

DECLARE  TemCursor CURSOR FOR
SELECT PropertyProfitField FROM PropertyProfitFields
WHERE PropertyProfitField NOT IN
      ('CloseInspectionDescription', 'PropertyProfitID', 'Active','PropertyProfitNumber','RehabMiscDescription','CloseMiscDescription'
      ,'UpdatedOn','UpdatedBy','SaleClosingDate','AcqPurchaseClosingDate','AcqCommissionTo','RehabSupervisionFeeTo')


OPEN TemCursor

FETCH NEXT FROM TemCursor
INTO @PropertyProfitField

SET @STRSQL = ''

WHILE @@FETCH_STATUS = 0
BEGIN

      SET @STRSQL = 'SELECT ' + @PropertyProfitField + ', PropertyProfit.' +  @PropertyProfitField + '  as [Proposed] ' +
                          ', (SELECT PropertyProfit.' +   @PropertyProfitField + ' ' +
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''ListingPrice'')  as [ListingPrice] ' +      
                            ', (SELECT PropertyProfit.' +   @PropertyProfitField + ' ' +
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit.Active = 1 AND PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''ListingPrice'')  as [CurrentAccepted] ' +      
                            ', (SELECT PropertyProfit.' +   @PropertyProfitField + ' ' +
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''ListingPrice'')  as [Final] ' +                                                                                 
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''Proposed'''
                                                     
      
      SET @STRSQL += ' UNION ALL '
      
      FETCH NEXT FROM TemCursor
      INTO @PropertyProfitField
END

CLOSE TemCursor
DEALLOCATE TemCursor

                    
                    
print left(@STRSQL,len(@STRSQL)-9)

EXECUTE sp_executesql @STRSQL
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

nativCommented:
In This case you could just move the 'UNION ALL' until after the FETCH.
But there is a better solution. I will add in a minute
FETCH NEXT FROM TemCursor
      INTO @PropertyProfitField
      
if @@FETCH_STATUS = 0
      SET @STRSQL += ' UNION ALL '

Open in new window

nativCommented:
Instead of using a cursor you can create the script in one select which will concatenate the results to one string.
SELECT @STRSQL = COALESCE(@STRSQL + ' UNION ALL ', '') + 'SELECT ' + PropertyProfitField + ', PropertyProfit.' +  PropertyProfitField + '  as [Proposed] ' +
                          ', (SELECT PropertyProfit.' +   PropertyProfitField + ' ' +
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''ListingPrice'')  as [ListingPrice] ' +     
                            ', (SELECT PropertyProfit.' +   PropertyProfitField + ' ' +
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit.Active = 1 AND PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''ListingPrice'')  as [CurrentAccepted] ' +     
                            ', (SELECT PropertyProfit.' +   PropertyProfitField + ' ' +
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''ListingPrice'')  as [Final] ' +                                                                                
                           'FROM  PropertyProfit INNER JOIN ' +
                           'PropertyProfit_PropertyProfitType ON PropertyProfit.PropertyProfitID = PropertyProfit_PropertyProfitType.PropertyProfitID ' +
                           'INNER JOIN PropertyProfitType ON PropertyProfit_PropertyProfitType.PropertyProfitTypeID = PropertyProfitType.PropertyProfitTypeID ' +
                           'WHERE PropertyProfit_PropertyProfitType.PropertyID = 1607 ' +
                           'AND  PropertyProfitType.PropertyProfitType = ''Proposed'''
                      FROM PropertyProfitFields
            WHERE PropertyProfitField NOT IN
        ('CloseInspectionDescription', 'PropertyProfitID', 'Active','PropertyProfitNumber','RehabMiscDescription','CloseMiscDescription'
        ,'UpdatedOn','UpdatedBy','SaleClosingDate','AcqPurchaseClosingDate','AcqCommissionTo','RehabSupervisionFeeTo')

Open in new window

nativCommented:
(Sorry for the delay - i had connectivity problems)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.