none
frustrating substring query problem RRS feed

  • Question

  •  

    Hi
    I have entries in my database which are unique order numbers. they look 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 numbers 0-9. It works great. What I get is:
    Paulie
    geroido
    geroido
    geroido
    lynnie

    However, because I know that geroido is one customer 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?

    Is there a way to put the above code into an sql query - something like

    SELECT DISTINCT(preg_replace('/[0-9]/', '', Ordernum);.  or similar so that I get only distinct occurences?

     

     

    [code]
          $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>");
                }
    [/code]

     


     

    Sunday, September 14, 2008 8:34 AM

Answers

  •  

    Are you getting any sysntax error?   try this

     

    select distinct a.*  from

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

    Sunday, September 14, 2008 11:25 AM
    Moderator
  • I have found the solution as follows:

     

    $query = "select distinct (cust) from

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

     

    I had to do a distinct(cust). This works perfectly. One small problem though. I need the CustFName, CustSName, StreetAddr, Town and county for display purposes. I tried the following but the query then stopped working

     

    $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";

    Sunday, September 14, 2008 11:51 AM

All replies

  •  

    Hi,

    preg_replace? I think its PHP function?  No idea about that.

    But what I understand i suggest you to ...

     

    SELECT  min(preg_replace('/[0-9]/', '', Ordernum)) as cust, CustFName, CustSName, StreetAddr,
                 Town, County from orderdetails

    group by CustFName, CustSName, StreetAddr,
                 Town, County

    Sunday, September 14, 2008 9:15 AM
    Moderator
  • Thanks Mangal9i

    I've managed to nearly get this working with the following:

     

    [code]

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

    Strings are like

    geroido20080829134435

    so it ignores the last 14 digits.

    It works well but is getting multiple returns like the following which is ok

    geroido

    geroido

    geroido

    geroido

    geroido

    lynnie

    lynnie

     

    What I now need is to wrap a query around the above one to give me distinct results from the results. Any ideas? I need just:

    geroido

    lynnie

    lynnie

    Sunday, September 14, 2008 9:33 AM
  •  

    Hi already gave you sample

     

    SELECT min(SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14)) as cust, CustFName, CustSName, StreetAddr,
                 Town, County FROM orderdetails

    group by CustFName, CustSName, StreetAddr,
                 Town, County

    Sunday, September 14, 2008 9:42 AM
    Moderator
  • Hi

    This is still giving me duplicates. If I could just wrap a further select distinct statement around this to give me distinct occurences of 'cust' then it would be great. Any ideas?

     

    SELECT min(SUBSTRING(Ordernum,1,LENGTH(Ordernum)-14)) as cust, CustFName, CustSName, StreetAddr,
                 Town, County FROM orderdetails

    group by CustFName, CustSName, StreetAddr,
                 Town, County


     

    Sunday, September 14, 2008 9:51 AM
  •  

    Ok try this

     

    select distinct(a.* ) from

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

    Sunday, September 14, 2008 10:32 AM
    Moderator
  • No records are returned now with

     

    $query = "select distinct(a.* ) from

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

     

    I know the inner select is returning records but the outer select not working. Has it got something to do with the 'as cust'. Isn't this where the values are stored?

    Sunday, September 14, 2008 10:57 AM
  •  

    Are you getting any sysntax error?   try this

     

    select distinct a.*  from

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

    Sunday, September 14, 2008 11:25 AM
    Moderator
  • Ok Mangal9i

    This works but the records are not distinct. There are still repeats

     

    $query = "select distinct a.*  from

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

     

    If the results of the inner query are stored in 'cust' should I not be looking for a distinct value there and how?

     

    Sunday, September 14, 2008 11:40 AM
  • I have found the solution as follows:

     

    $query = "select distinct (cust) from

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

     

    I had to do a distinct(cust). This works perfectly. One small problem though. I need the CustFName, CustSName, StreetAddr, Town and county for display purposes. I tried the following but the query then stopped working

     

    $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";

    Sunday, September 14, 2008 11:51 AM