Thank you in advance! Wanting to know the best way in Marketing Cloud to remove records in a data extension that only have a number under the street address field rather than a number and the actual street name.

So for example, wanting to remove records which only have: Street_name: 12

And only include examples that have Street_name: 12 Flood Street.

Thank you!

  • Maybe it would be sufficient when you exclude everything that has a length less than 4 with an sql clause. So you do something similar to WHERE LENGTH(Street_name) > 4. Most of the numbers are not higher than thousands and if there is a street in it, it would mostly be longer than 4 characters – Johannes Schapdick Mar 8 at 9:48

Depending on your business needs/wants, I have a couple ideas that may help you with this. There is no definitive way to handle this programmatically - especially if you need to consider international addresses, etc.

First solution is to have a conditional that looks first to see if there is a space inside the string and then verifies if the length of the substring after the space is greater then 4 (arbitrary number I added to verify its a real address and not just an apt letter or similar - can change based on your use case).

Like so:

SELECT subscriberkey,
WHERE CHARINDEX(' ', address) > 0 AND LEN(RIGHT(address,LEN(address) - CHARINDEX(' ', address))) > 4

The other solution is to check how many spaces are inside the string. For instance, it would need at least 2 spaces for it to be a real address. E.g. 121B Baker Street has 2 spaces, where 121 Street only has one. The one consideration is if they do not have a house number, but only a street name e.g. Baker Street of the input is incorrect e.g. 121B BakerStreet then it will fall off.

SELECT subscriberkey,
WHERE LEN(address) - LEN(REPLACE(address, ' ', '')) > 1

I find the above two to be the simplest and most universal options around this. There are other options, but they are more niche and require specific business rules on input data or data formation, so I won't go into them here.


You can achieve this by using ISNUMERIC() function in SQL

SELECT column_name from [DE Name] where ISNUMERIC(Street_name) = 0

But here are few considerations like

  1. If it contains just numbers, it will work, for eg. '12'
  2. If it contains numbers and space after that, it will work, for eg. '12 '
  3. If it contains numbers and space and then again numbers then it does not treat it as just numbers, for eg. '12 12' and will return false value. You can play around with this function for more results but as far as your business requirement is concerned, this should solve the problem.

Hope this helps!

  • Thank you so much @Nishchal Agarwal! – Jocasta Anderson Mar 8 at 12:46
  • 1
    As you might have cases where a house number also contains non numerical characters - e.g. 12A, you can't fully rely on ISNUMERIC() @JocastaAnderson – Lukas Lunow Mar 8 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.