Solved

mySQL substring and mid function

Posted on 2009-04-11
1
541 Views
Last Modified: 2012-05-06
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
Comment
Question by:Slythie
1 Comment
 
LVL 45

Accepted Solution

by:
Kdo earned 125 total points
Comment Utility
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Creating and Managing Databases with phpMyAdmin in cPanel.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now