Solved

mySQL substring and mid function

Posted on 2009-04-11
1
543 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Could you check what is preventing this MySQL query to correctly running? 2 61
Mysql Crashing Intermittently 16 82
showing numeric numbers 2 33
RDBMS and No sql database 4 46
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

17 Experts available now in Live!

Get 1:1 Help Now