SQL Starts With List

Kinetic ERP
,
1

Hello all,

I’m new to SQL and have very similar application. The exception is that I want to return a list of Customer IDs that “start with” given letters. (e.g. starts with ‘ABI’ or ‘AG’ or ‘EH’ or etc.)

Trying to work from @John_Mitchell’s solution I’m thinking an expression like…

WHERE (Customer.CustID LIKE ‘ABI%’ OR
Customer.CustID LIKE ‘AG%’)

but that is failing.

Any ideas?

Thanks as always.

2

You should start a new thread… and reference this one in it…it has been 4 years solved!

3

I split it into a new topic.

4

Sorry gentlemen. And thank you.

5

Switch to “specified constant”

sc
sc859×195 25.2 KB

6

If you need expression for some reason, wrap it in single quotes

Company LIKE ‘MS%’

7

@klincecum Trying to avoid Specified because I have a large list and don’t want a long list of Criteria.
Would rather be able to manage 1 expression.

8 
Field: Company

Operator: Like

Expression: 'MS%' or ABCCode.Company LIKE '%4'

or

Field: Company

Operator: =

Expression: ABCCode.Company AND ABCCode.ABCCode = 'A' OR ABCCode.ABCCode LIKE 'B%'

Clear as mud ?

9

This works. Returns all customers with CustID that starts with specified pattern.

Operation: LIKE

Filter:

‘ABI%’ OR
Customer.CustID LIKE ‘AG%’ OR
Customer.CustID LIKE ‘BFG%’ OR
Customer.CustID LIKE ‘BHT%’ OR
Customer.CustID LIKE ‘CS%’ OR
Customer.CustID LIKE ‘USH%’ OR
Customer.CustID LIKE ‘USC%’ OR
Customer.CustID LIKE ‘CIG%’

1 Like
10

Maybe this will make more sense:

Field: Company

Operator: =

Expression: 

Constants.CompanyID AND
Customer.CustID LIKE 'ABI%' OR
Customer.CustID LIKE 'AG%' OR
Customer.CustID LIKE 'KLIN%' OR
Customer.CustID LIKE 'TWOO%'
11

@klincecum I see now. Not how I read that before.
Thank you!

12

Some advice I’ve seen here is that it can be more efficient to tag those records instead of relying on “smart” codes to do selections. If I want to do something with a group of customers, parts, suppliers, etc., I can:

  • Add a Data Tag
  • Use a built-in Field
  • Use Attributes
  • Add a UD field

It’s easier to maintain the code looking for one thing than constantly changing my selection statement. It also guards from people not using the “smart” code correctly. Food for thought…

1 Like
13

Much appreciated @Mark_Wonsil.
I will take into consideration.

14

Yeah that second way is less clear, but it keeps your “LIKE” clauses together.