Backwards SQL Query for Substring Search

Question

This is a little convoluted, but hopefully it makes sense. I'm trying to figure out if there's a query or some trickery available to do the following. (I'm actually using Netsuite, so it's not exactly SQL, but they support a base subset of SQL commands, so if it's possible, I can probably find a way to do it.)

I have a table (custom record in Netsuite) that contains a few fields for customer addresses that we use as fraud checks. Fraud customers tend to use various incantations of the same fake address by adding bogus suite numbers or other suffixes. What we've done is set up our records in our table to have the common portion of the street address (like "123 Main Street") without the extra stuff they tack on (like "suite 12345"). When a fraud order is placed, we take the address the customer entered and try to find any records in our table where the street address field of the table is itself a substring of the address entered by the customer on the order. This is backwards from a typical substring search wherein you'd try to find a record where a field in the table CONTAINS a substring. Instead, I'm trying to find any records in the table where a field IS a substring of some constant string.

Does this make sense? Is it possible or will I have to do some magic on my own?

Show us some sample data and expected result and we will do our magic. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza

Mike Christie Mike Christie · Accepted Answer · 2016-07-15T15:42:58

Here's what I've done for similar situations. Suppose your two fraud addresses are in a fraud_addresses table, with a street_address column. I'm going to assume for the sake of clarity that you're doing this with a parameter, @newaddress; if you have a tableful of addresses to validate, this could be adapted.

select *
  from fraud_addresses f
 where charindex(f.street_address,@newaddress) > 0;

This lists all the fraud addresses that can be found as substrings of the new address.

Backwards SQL Query for Substring Search

1 Answers