0

In marketing cloud, I am trying to find records where the post code does not match in two different fields but I'm running into an issue due to different spacing making the same postcode look different. Is there a way I can remove all spacing in both fields? Similar to TRIM in Excel but within the string rather than just the ends.

select *
from Postcode_match
where PostcodeA != PostcodeB
4
  • 1
    Can you please try with "SELECT REPLACE(Your Attribute, ' ', '')". ?
    – Naveen VM
    Apr 24, 2020 at 13:29
  • That worked perfectly thank you!
    – nc_analyst
    Apr 24, 2020 at 13:54
  • Also you might consider resolving the root problem - data quality. At least if its in your hands.
    – DonL
    Apr 24, 2020 at 14:12
  • You are Welcome @nc_analyst!. Please mark it as the right answer. It will help for others in future. Thanks!
    – Naveen VM
    Apr 24, 2020 at 14:33

1 Answer 1

2

Please update the Query like below:

Select Replace(PostcodeA,' ','') as PostcodeA, Replace(PostcodeB,' ','') as PostcodeB
from Postcode_match
where PostcodeA != PostcodeB

This will remove all the space in the attribute.

2
  • 1
    This throws an error. Something about unknown column Your Attribute to remove space?? Hahahaha ... j/k, but you might consider using the value in OP's question :)
    – Bill Mote
    Apr 24, 2020 at 14:55
  • 1
    Hahaha that's funny @BillMote. I have edited my answer as per the question. Thanks for your suggestion!
    – Naveen VM
    Apr 24, 2020 at 15:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .