Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mySQL substring and mid function

Posted on 2009-04-11
1
Medium Priority
?
553 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 375 total points
ID: 24122761
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 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