0

How can I use trim, or another function, to remove a space in the middle of a column that I am trying to join 2 tables on?

Ex:
....from Stage.FactTravelIBank ft
     join Dim.Mileage m
      on m.market = ft.itinerary

where table m has market abcdef and ft has itinerary abc def

I've only been able to find examples where trim is used with spaced at the beginning or end, or is trimming a character.

courty340
  • 101
  • 3
  • 16
  • 1
    Have you considered `replace(ft.itinerary,' ','')` ? – Stu Sep 29 '21 at 13:33
  • TRIM does only remove leading and trailing characters, not somewhere in the middle. – jarlh Sep 29 '21 at 13:33
  • 1
    https://stackoverflow.com/questions/10432086/remove-all-spaces-from-a-string-in-sql-server/10432124 would work. – Aron Sep 29 '21 at 13:33
  • By the way, if you are joining on mutated strings... you are going to have performance issues. The database should be designed in a relational way that allows joining tables without string manipulation. – Aron Sep 29 '21 at 13:34
  • 1
    Even if possible I would consider this an unreliable query. I would better do a data clean up. – The Impaler Sep 29 '21 at 13:34
  • @Aron Yes I think this will work, Thanks! – courty340 Sep 29 '21 at 13:40

0 Answers0