Link to home
Start Free TrialLog in
Avatar of Mahesh Yadav
Mahesh YadavFlag for India

asked on

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.
Avatar of nativ
nativ

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.
By the way.
Set @Str = left(@str,len(@str)-9)
will give you the same results if you prefer.
Avatar of Mahesh Yadav

ASKER

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
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

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

ASKER CERTIFIED SOLUTION
Avatar of nativ
nativ

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial