• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

mySQL substring and mid function

Dear Experts,

I have a database table to store sale details with invoice IDs. The format for these invoice IDs are as follows : SEUSC003-1/CEI000013. The first S refers to Sale, EU refers to End User, SC003 refers to supplier, the number 1 refers to invoice number for that particular supplier.  CEI000013 refers to customer code. I want a query to compare whether SC003 have been entered or not and I want that number 1 to auto increment later on. I'm using this on VB6 btw. I have the following query just to check whether SC003 have been entered or not:

select * from saledetails where supplier_code = substr(invoice_id, 8, 4).

is the query correct? It should return all the saledetails where supplier code is (eg. SC003) right? or is my query totally wrong?

Thx for the help experts
0
Slythie
Asked:
Slythie
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Slythie,

Be very careful about building too much information into a single data item.  That can really limit your flexibility later on.

I'd modify the table so that the current Invoice Number is still kept, but you also keep the parts from which it's built.  You can then index each of the columns so that queries against them become very, very fast.

Building all of this information into the Invoice Number string, and using that data later, will require at least a full index scan to look for specific items, and depending on your exact usage may require a full table scan.

By the way, you want substr (invoice_id, 4, 5) for your current needs.


Good Luck,
Kent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now