CODEWITHSUNDEEP
sqlsql-server
Jakesan
Jakesan

Reputation: 113

SQL: How to find product codes

We have a database (MSSQL) that contains products table, where products are put in a way that in the product id the first 4 letters is a customer specific code. For example:

etc.

The first 4 letters is the customer code, while the later part is the product code that can overlap. The same customer can order products with the same code, but we keep our stock separate like this. In database at least.

Is there a way to make a query that would tell that for example product 07004400B exists in more than one customers entry?

Upvotes: 1

Views: 1510

Answers (2)

Bohemian
Bohemian

Reputation: 425258

Use substring() to extract the produce code, and group-by with having to find the hits:

select substring(product_id, 5, len(product_id)) code
from products
group by substring(product_id, 5, len(product_id))
having count(*) > 1

If you want a specific one, add a where clause:

select substring(product_id, 5, len(product_id)) code
from products
where substring(product_id, 5, len(product_id)) = '0700400B'
group by substring(product_id, 5, len(product_id))
having count(*) > 1

Upvotes: 1

Francois
Francois

Reputation: 10978

Use "like" keyword to find all products with ID ending with 07004400B.

select count(*) from TABLE where product_id like '%07004400B'

Upvotes: 4

Related Questions