I need to create a custom query using specific characters from database

What i have is a rather large database of flight numbers based on divisions. the flight number is entered as following eg: CCC-NOC001 where the number breaks down as follows CCC airline Code NOC division Code and 001 actual flight number.

Now i am trying to create a filter that will select flight number from the schedule based on the division code. now to clarify the way the flight numbers are entered into the database is in 2 fields the first being code which is always entered as CCC and the flight number which would be NOC001 etc.

So what i need if it is possible is to strip the numbers from the end of the flight number so that all that is searched for is the NOC or one of the other division codes currently there are 9 separate divisions. And i would like to be able to filter the queries so that if from the drop down list they select NOC then only the flight numbers with NOC will show in the table.

CCC-NOC001
CCC-NOC002
....

Any help you can give me would be greatly appreciated

 

Hi, just a suggestion: why didn't you use different mysql columns for all parts-the final result will be simply concat and you'll not have any troubles.

MySQL has not preg_replace...
Regards

 

Thanks for the reply but that would mean rewriting about 15 different scripts to work with that change. But i think you may have put me in the right direction though. I was reading on the page you linked and came across LEFT and LTRIM would those work for my situation? If so how would i code those into my query.
This is the block of code that needs to be altered to make the function i need work. I kinda figure using the LTRIM to remove the - from the beginning of the flight number, and the LEFT to select the first 3 characters in the str. But I am not sure how to incorporate that into the code.

/**
     * Get all of the flight divisions
     */
    public static function getAllFlightNum()
    {
        $key = 'all_flight';
        $all_flight = CodonCache::read($key);
        
        if($all_flight === false)
        {
            $sql = 'SELECT * FROM ' . TABLE_PREFIX .'schedules 
                    ORDER BY `flightnum` ASC';
                    
                
            $all_flight= DB::get_results($sql);
            
            CodonCache::write($key, $all_flight, 'long');
        }
        
        return $all_flight;
    }

PHP:

right now it returns all flights in the database

 

Just pass $all_flight in php preg_replace before function returns-better than mysql functions, because I'm not sure the length is and will be (in the future-when you add new data in DB) always constant length.

 

OK, here it's in MySQL:

DROP TABLE IF EXISTS TESTING;

CREATE TABLE TESTING(S VARCHAR(255));
INSERT INTO TESTING VALUES(
'CCC-NOC001'),
('CCC-NOC002');

SELECT SUBSTRING_INDEX(S,'-',1) AS CCC,SUBSTRING_INDEX(S,'-',-1) AS NOC FROM TESTING;

Code (markup):

Edit: Please, check ur PMB-sent you another example!