能否使用Trim函数处理字段中间空格以实现表关联？及如何移除字段中间空格完成两表关联？

Can the TRIM() function handle middle spaces for table joins? #

• Short answer: No. The TRIM() function only targets leading and trailing whitespace (or specified characters) in a string—it won’t touch spaces that sit between other characters. In your example, running TRIM(ft.itinerary) would still return abc def instead of abcdef, so it won’t match the m.market value of abcdef for the join.

How to remove middle spaces for table joins? #

The straightforward fix here is using the REPLACE() function, which lets you substitute every occurrence of a space with an empty string. Here’s how to adjust your query:

SELECT *
FROM Stage.FactTravelIBank ft
JOIN Dim.Mileage m 
  ON m.market = REPLACE(ft.itinerary, ' ', '')

Bonus: Performance Optimization Tip #

If you run this join frequently, using REPLACE() directly in the join condition can slow things down—databases can’t use existing indexes on ft.itinerary when you apply a function to it (they have to compute the space-free value for every row on the fly). To fix this:

1. Add a persisted computed column to your Stage.FactTravelIBank table to store the pre-calculated space-free version of itinerary:

   ALTER TABLE Stage.FactTravelIBank
   ADD itinerary_no_spaces AS REPLACE(itinerary, ' ', '') PERSISTED;
   

2. Create an index on this new computed column to speed up joins:

   CREATE INDEX IX_FactTravelIBank_ItineraryNoSpaces
   ON Stage.FactTravelIBank(itinerary_no_spaces);
   

3. Now your join can leverage the indexed column for faster lookups:

   SELECT *
   FROM Stage.FactTravelIBank ft
   JOIN Dim.Mileage m 
     ON m.market = ft.itinerary_no_spaces;
   

内容的提问来源于stack exchange，提问作者courty340