Why would anyone want to check a string for numbers in NetSuite Saved Searches? I was recently tasked with a roundabout way of checking for fraudulent customers based on bogus email addresses. These email addresses all contained a name followed by a series of numbers, i.e. georgewashington31523154@gmail.com.

The best way I could come up with was to use a CASE WHEN statement using the field FORMULA (numeric). I found a lot of info on using wildcards in conjunction with the LIKE command in SQL. None of them worked to cover this situation.

My first attempt was to check the {email} field with LIKE ‘%[0-9]%’, but it could not find this range of numbers. But, after testing, I found that ‘%1%’ would find the number ‘1’ if present in the email address. So, I ended up making a lengthy CASE WHEN statement that looked like this:

CASE WHEN {email} LIKE '%1%' THEN '1' WHEN {email} LIKE '%2%' THEN '1' WHEN {email} LIKE '%3%' THEN '1' WHEN {email} LIKE '%4%' THEN '1' WHEN {email} LIKE '%5%' THEN '1' WHEN {email} LIKE '%6%' THEN '1' WHEN {email} LIKE '%7%' THEN '1' WHEN {email} LIKE '%8%' THEN '1' WHEN {email} LIKE '%9%' THEN '1' WHEN {email} LIKE '%0%' THEN '1' ELSE '0' END

While this is not a clean formula, it worked. If any number 0-9 is present, it returns a 1. I put this formula under SUMMARY for criteria that would ignore anything with a sum of 0.

Definitely not a perfect solution, but it narrowed down our search to a manageable amount to monitor. Some valid customers were present, those with say a birth year in their email. But overall, this saved search gave us enough flexibility to help find these bogus customers before an order went through.