frustrating sql substring problem

[geroido]

Hi

I have entries in my database which are unique order numbers. they look like this like this:

Paulie20080913202737

geroido20080913105404

geroido20080913105404

geroido20080825105322

lynnie20080825111708

 

What I want to do is extract only the first part i.e. geroido, lynnie or paulie. I am achieving that with the sql statement below - the main line being    $row->cust = preg_replace('/[0-9]/', '', $row->cust);. This extracts everything except number 0-9. It works great. What I get is:

Paulie

geroido

geroido

geroido

lynnie

 

However, because I know that geroido is one customers with many orders, what I really want to do is just display a distinct value so I want to have just

Paulie

geroido

lynnie

 

Can you help me out?

 

      $query = "SELECT  DISTINCT(Ordernum) as cust, CustFName, CustSName, StreetAddr, 
             Town, County from orderdetails group by Ordernum "; 
$results = mysql_query($query, $link) or die("Sorry, the database is unavailable"); 
if(mysql_num_rows($results) > 0){ 
            ?><TABLE border=2><TR><TD><Font size=3 color="black">Select customer</TD><TD><Font size=3 color="black">Business name</TD></FONT><TD><Font size=3 color="black">Customer name</FONT></TD><TD><Font size=3 color="black">Town</TD></FONT><TD><Font size=3 color="black">County</TD></FONT><TD><Font size=3 color="black">Service type</TD></FONT></TR><?
while($row = mysql_fetch_object($results)){ 
   $row->cust = preg_replace('/[0-9]/', '', $row->cust);
              echo ("<tr><td>");
              echo "<a href='cls_indcust.php?id=" .$row->cust. "'>Send comment</a>";
              echo ("</td><td>");     
              echo $row->cust;
              echo ("</td><td>");
              echo $row->CustFName . " " .$row->CustSName;
              echo ("</td><td>");
              echo $row->StreetAddr;
              echo ("</td><td>");
             echo $row->Town;
            echo ("</td><td>");
             echo $row->County;
              echo ("</td>");
             echo ("</tr>");
              }
              } 
              }
              else{ 
                echo("<i>There are currently have no customers in the database</i>"); 
            }

[fenway]

The problem is that they're unique before you clean them up, so DISTINCT won't help you.  I'm surprised your design doesn't include a "clean" customer name (or at least a FK).

[geroido]

yeah, I know that. It's a problem but too late now. I'm on a deadline

Do you know of a mysql substring function where I could scan these records, extract the first part and then just show the distinct ones?

[PFMaBiSmAd]

You can use TRIM(TRAILING '0123456789' FROM Ordernum) to get just the name part, but you should use this for the purpose of populating a new column from your existing data that only contains the name part. Are CustFName, CustSName unique and is the name part of Ordernum actually duplicating information that CustFName, CustSName provides?

 

Also, DISTINCT operates on everything that is SELECTED, so it is probably not what you want anyway. What are you trying to accomplish?

[geroido]

Really it sounds simple but as usual is not.  A field in my table (Ordernum) contains values like:

geroido20080913105404

geroido20080825105322

paulie20080623204307

lynnie20081102175032

 

I want to go through these records, extract just the name part(geroido, paulie, lynnie etc.) but I want just a distinct result. So in the example above, although there are two instances for geroido, I just want geroido to be returned once and that goes for any other name that has multiple entries.

[PFMaBiSmAd]

This should work -

 

SELECT DISTINCT TRIM(TRAILING '0123456789' FROM Ordernum) FROM orderdetails

 

[PFMaBiSmAd]

Update to the above -

 

The mysql trim() function won't trim all digits found in the given '0123456789' string (the way the php trim() function would.)

 

However, the following does work (tested) -

 

SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) FROM orderdetails

[geroido]

Hi

I've altered my sql to the following but still getting all of the records not getting distinct values. It is working however to extract just the first part.

 

$query = "SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr,

            Town, County FROM orderdetails";

 

 

Any ideas

[geroido]

Hi guys

The previous post is wrong. I am in fact getting distinct values but they are distinct orders so if the table holds the following 4 values, I still get geroido being returned twice

geroido20080913105404

geroido20080913105404

geroido20080913105404

geroido20080825105322

 

Can I wrap another query around this one to then get just distinct values from the result of the below query? So the query below works well but now I want to put the results through a second query to get distinct values.

 

$query = "SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr, 
             Town, County FROM orderdetails";

[geroido]

Hi

The nested query below gives me results like

geroido

geroido

geroido

lynnie

lynnie

paulie

paulie

 

The outer query then gives me just the distinct values like:

geroido

lynnie

paulie

 

It all works well but I need the CustFName, CustSName, StreetAddr, Town, County also. Can you tell me what to add to the outer query to retrieve these values aswell? I've tried:

 

$query = "select distinct (cust), CustFName, CustSName, StreetAddr,  Town, County  from

(SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr,  Town, County FROM orderdetails )A ";

 

but the query stops working.

 

 

$query = "select distinct (cust) from 
(SELECT DISTINCT SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14) as cust, CustFName, CustSName, StreetAddr,  Town, County FROM orderdetails )A ";