Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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