1

I am trying to compare two tables (Current Software, Recommended Software) that fraught with trailing 'blank miscellaneous' strings in most records being compared. For some reason I cannot alter or delete the trailing invisible spaces or blank characters in the fields being compared in SQL server (unless I am doing something wrong).

I've even tried exporting the effected table records to MS excel, manually removing the trailing blank strings, deleting it physically using the delete key, and updating the tables. No such luck and totally baffled. So I've decided to have the query perhaps "TRIM" the records being compared on the fly.

Question: Is there a way I can simply modify the following query to compare two tables with NO trailing blank strings in either records in SQL?

This is the query

SELECT        dbo.VIEW_CURRENT_SOFTWARE_BYVER.SRV_ID_SSI, dbo.VIEW_CURRENT_SOFTWARE_BYVER.SRV_MACID, dbo.VIEW_CURRENT_SOFTWARE_BYVER.SRV_DN, dbo.VIEW_CURRENT_SOFTWARE_BYVER.DISPLAYNAME, 
                         dbo.VIEW_CURRENT_SOFTWARE_BYVER.DISPLAYVERSION, dbo.VIEW_CURRENT_SOFTWARE_BYVER.VALOVERRIDE, dbo.VIEW_RECOMMENDED_SOFTWARE_BYVER.SRV_UPDATEDVERSION, 
                         CASE WHEN [VIEW_CURRENT_SOFTWARE_BYVER].[DISPLAYVERSION] = '' AND 
                         [VIEW_RECOMMENDED_SOFTWARE_BYVER].[SRV_UPDATEDVERSION] = '' THEN 'No Version Available' WHEN [VIEW_CURRENT_SOFTWARE_BYVER].[DISPLAYVERSION] IS NOT NULL AND 
                         [VIEW_RECOMMENDED_SOFTWARE_BYVER].[SRV_UPDATEDVERSION] IS NULL 
                         THEN 'Evaluation Required' WHEN [VIEW_CURRENT_SOFTWARE_BYVER].[DISPLAYVERSION] = [VIEW_RECOMMENDED_SOFTWARE_BYVER].[SRV_UPDATEDVERSION] THEN 'Current' WHEN [VIEW_CURRENT_SOFTWARE_BYVER].[DISPLAYVERSION]
                          != [VIEW_RECOMMENDED_SOFTWARE_BYVER].[SRV_UPDATEDVERSION] THEN 'Requires Update' ELSE 'Error' END AS SRV_RECOMMENDATION, dbo.VIEW_CURRENT_SOFTWARE_BYVER.RELEASE, 
                         dbo.VIEW_CURRENT_SOFTWARE_BYVER.PUBLISHER, dbo.VIEW_CURRENT_SOFTWARE_BYVER.INSTALLDATE
FROM            dbo.VIEW_CURRENT_SOFTWARE_BYVER LEFT OUTER JOIN
                         dbo.VIEW_RECOMMENDED_SOFTWARE_BYVER ON dbo.VIEW_CURRENT_SOFTWARE_BYVER.DISPLAYNAME = dbo.VIEW_RECOMMENDED_SOFTWARE_BYVER.SRV_CMBNAME
GO

Where

VIEW_CURRENT_SOFTWARE_BYVER.DISPLAYNAME (varchar(max) and VIEW_RECOMMENDED_SOFTWARE_BYVER.SRV_CMBNAME (char(215) fields contain the annoying trailing blank strings that result in them not marrying up correctly in the query.

enter image description here

Thanks in advance

NanoNet
  • 218
  • 3
  • 11
  • What is the data type of SRV_CMBNAME ... varchar or char ? – John Cappelletti Jun 08 '21 at 03:28
  • Are you sure they're space characters? If they're output from some software inventory system they could well include embedded carriage return (0x0d) or linefeed (0x0a) characters. Try `cast(... as varbinary(max))` on one of the problem fields and see if it's all space (0x20) characters at the end. – AlwaysLearning Jun 08 '21 at 03:28
  • @JohnCappelletti Updated the string types. CMBNAME is char(215), and DISPLAYNAME is varchar(max)) – NanoNet Jun 08 '21 at 03:48
  • 1
    char(x) will reserve the trailing spaces. Just for fun try Select convert(char(10),'[')+']' While varchar(n) will trim trailing spaces – John Cappelletti Jun 08 '21 at 03:50
  • If you convert your char(215) to varchar(215) ... you will still have to perform an update to trim the existing trailing spaces. ... this would be a one time event. – John Cappelletti Jun 08 '21 at 03:54
  • @JohnCappelletti So I've changed BOTH affected columns to varchar(215) with no issues or loss of data, and strangely enough the SRV_CBMNAME retrained the annoying trailing spaces while DISPLAYNAME does not have any trailing strings.. Any suggestions? – NanoNet Jun 08 '21 at 04:08
  • 1
    @JohnCappelletti oh I see.. ok let me try that TRIM measure again.. and see if it will work! – NanoNet Jun 08 '21 at 04:09
  • @JohnCappelletti SRV_CMBNAME still gives me the trailing blank strings despite the type change and manual reformatting to remove the trailing strings. Is there a statement that I can use to reprocess all the affected strings to drop the trailing blank strings? – NanoNet Jun 08 '21 at 04:19
  • Before you go down that path, it looks like you are pulling data from a VIEW. I would check the construction of the view and any dependencies. If no resolution, I would then check for white-space characters https://stackoverflow.com/questions/43148767/sql-server-remove-all-non-printable-ascii-characters/43149139#43149139 – John Cappelletti Jun 08 '21 at 04:25
  • Random thought. You may have to re-compile the view – John Cappelletti Jun 08 '21 at 04:29
  • 1
    @JohnCappelletti rather than try that replace to the nth degree.. I dropped all the affected tables and just reinported my tables with all the fixings.. no more leading or trailing blank or misc characters.. my query is still broken but at least no more annoying trailers.. I'll keep researching as to why I am not making a clean match up between my columns. Thanks again. – NanoNet Jun 08 '21 at 04:57

1 Answers1

0

I dropped all the affected tables and just re-inported my tables with all the fixings.. no more leading or trailing blank or misc characters.. my query is still broken but at least no more annoying trailers.

NanoNet
  • 218
  • 3
  • 11