Hey folks,
I’m stuck with a tricky SQL problem. I need to find addresses that have any version of ‘P/O Box’ in them. The catch is, there are like 14 different ways it can be spelled (PO, BOX, P O, P/O, etc.). These variations are stored in a separate table.
I tried using CHARINDEX, but it’s picking up false positives like ‘POLAR ST.’ Ugh!
Is there a way to do this without full-text search? I just want exact matches for these specific spellings.
Any ideas would be super helpful. Thanks!
Have you considered using a combination of LIKE and OR operators? You could create a query that checks for each variation individually. It might look something like this:
SELECT *
FROM addresses
WHERE address LIKE ‘% P/O Box %’
OR address LIKE ‘% PO Box %’
OR address LIKE ‘% P.O. Box %’
– Add more variations here
This approach allows for exact matching of each variation. To make it more efficient, you could dynamically generate the SQL from your variations table. While it’s not as elegant as regex, it’s straightforward and should avoid false positives. Just ensure you include spaces before and after each variation to prevent partial matches.
hm, interesting problem! have u thought about using a table-valued function? you could pass in ur address column and variations table, then do some fancy string manipulation inside. might be more flexible than a big ol’ WHERE clause. what do u think? could be fun to try!
hey max, have you tried using a regex pattern match? you could build a pattern from your variations table and use it with LIKE. something like:
SELECT * FROM addresses WHERE address LIKE ‘%[PO BOX]%[/ ]%[BOX]%’
might need some tweaking, but could work. good luck!