CODEWITHSUNDEEP
mysqlsql
Ksmnaut
Ksmnaut

Reputation: 5

How to find missing numbers within a column of strings

I'm trying to find unaccounted for numbers within a substantially large SQL dataset and facing some difficulty sorting. By default the data for column reads

'Brochure1: Brochure2: Brochure3:...Brochure(k-1): Brochure(k):'

where k stands in for the number of brochures a unique id is eligible for. Now the issue arises as the brochures are accounted for a sample updated data would read 

'Brochure1: 00001 Brochure2: 00002 Brochure3: 00003....'

How does one query out the missing numbers, if in the range of number of say 00001-88888 some haven't been accounted next to Brochure(X):

Upvotes: 0

Views: 151

Answers (1)

Lis
Lis

Reputation: 624

The right way: You should change the structure of your database. If you care about performance, you should follow the good practices of relational databases, so as first comment under your question said: normalize. Instead of placing information about brochures in one column of the table, it's much faster and more clear solution to create another table, that will describe relations between brochures and your-first-table-name

<your-first-table-name>_id    | brochure_id
----------------------------+---------------
1                           | 00002
1                           | 00038
1                           | 00281
2                           | 28192
2                           | 00293
...                         | ...

Not mention, if possible - you should treat brochure_id as integer, so using 12 instead of 0012.

The difference here is, that now you can make efficient and simple queries, to find out how many brochures one ID from your first table has, or what ID any brochure belongs to. If for some reason you need to keep the ordinal number of every single brochure you can add a column to the above table, like brochure_number.

What you want to achieve (not recommended): I think the fastest way to achieve your objective without changing the db structure, is to get the value of your brochures column, and then process it with your script. You really don't want to create a SQL statement to parse this kind of data. In PHP that wolud look something like this:

// Let's assume you already have your `brochures` column value in variable $brochures

$bs = str_replace(": ", ":", $brochures);
$bs = explode(" ", $bs);
$brochures = array();
foreach($bs as $b)
    $brochures[substr($b, 8, 1)] = substr($b, strpos($b, ":")+1, 5);

// Now you have $brochures array with keys representing the brochure number, 
// and values representing the ID of brochure.

if(isset($brochures['3'])){
    // that row has a defined Brochure3
}else{
    // ...
}

Upvotes: 1

Related Questions