ScottyDoesKnow ScottyDoesKnow - 18 days ago 5
SQL Question

Is there any built-in Function in SQL to ignore white space when comparing strings

I'm trying to fetch records in SQL server with a query like below.

SELECT TDC_ROW_NUMBER
, TDC_TD_PARTCODE
FROM TD_DATA_PACK_CONTENTS
WHERE TDC_IDP_ID = 841
AND TDC_TREE_FAMILY_CLASSIFICATION + ', ' + TDC_TREE_CLASSIFICATION + ', ' + TDC_TREE_SUB_CLASSIFICATION + ' , ' + 'A' IN
( SELECT DISTINCT DPC_Level1 + ', ' + DPC_Level2 + ', ' + DPC_Level3 + ', ' + DPC_Status
FROM DATA_PACK_CATEGORIES )
AND TDC_TRADE_PRICE > 0.99
AND TDC_STATUS = 'RDY'


Now the problem i have is the inner query has some records where there are leading or trailing whitespace characters.

So a record looks
'Peripherals, Monitors, Monitors - Medical , A'
and the other string looks like
'Peripherals, Monitors, Monitors - Medical, A'
.

Obviously, this fails when comparing. So is there any function wherein I can trim all the whitespace characters in the entire string?

Answer

Use REPLACE():

REPLACE(TDC_TREE_FAMILY_CLASSIFICATION + ', ' + TDC_TREE_CLASSIFICATION + ', ' + TDC_TREE_SUB_CLASSIFICATION + ' , ' + 'A', ' ', '') IN
               (SELECT REPLACE(DPC_Level1 + ', ' + DPC_Level2 + ', ' + DPC_Level3 + ', ' + DPC_Status, ' ', '')
                 FROM DATA_PACK_CATEGORIES )

You can use the trim functions if you only care about spaces at the beginning/end of a string (actually, spaces at the end are usually ignored ignored anyway).

You can extend the above for additional characters by nesting REPLACE() calls.