On Thu, 7 Oct 2004 12:42:36 +0100, James Smith <[EMAIL PROTECTED]> wrote:
> With the exception of the missing "," after the shelf that works perfectly,
> thanks.
>
> --
> Jay
>
>
>
> > -----Original Message-----
> > From: Michael Traher [mailto:[EMAIL PROTECTED]
> > Sent: 07 October 2004 12:29
> > To: CF-Talk
> > Subject: Re: Query problem
> >
> > how about
> >
> > SELECT sum(quantity) as TotalQuantity,
> > RIGHT( '0' + SUBSTRING_INDEX(location , '.' , 1),2) as rack,
> > RIGHT('0' + SUBSTRING_INDEX(SUBSTRING_INDEX(location
> > , '.' , 2), '.' , -1),2) as shelf
> > RIGHT( '0' + SUBSTRING_INDEX(location , '.' , -1),2)
> > as box FROM stockquantities WHERE Quantity > 0 GROUP BY rack,shelf,box
> >
> > untested! I don't even have MYSQL but have a look here
> >
> > http://dev.mysql.com/doc/mysql/en/String_functions.html/
> >
> > SUBSTRING_INDEX(str,delim,count)
> >
> > Returns the substring from string str before count
> > occurrences of the delimiter delim. If count is positive,
> > everything to the left of the final delimiter (counting from
> > the left) is returned. If count is negative, everything to
> > the right of the final delimiter (counting from the right) is
> > returned.
> >
> > mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
> > -> 'www.mysql'
> > mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
> > -> 'mysql.com'
> >
> > HTH
> > Mike
> >
> >
> > On Thu, 7 Oct 2004 11:22:45 +0100, James Smith
> > <[EMAIL PROTECTED]> wrote:
> > > I have a fairly simple query that needs some modifications that are
> > > beyond me.
> > >
> > > Currently it reads.
> > >
> > > SELECT sum(quantity) AS TotalQuantity, Location FROM
> > stockquantities
> > > WHERE Quantity > 0 GROUP BY location
> > >
> > > Unfortunately the location field is text in the format
> > xx.xx.xx where
> > > x is a number, for example 01.01.01 or 34.12.02 which means
> > rack 34,
> > > shelf 12, box 2. The problem is that being text 01.01.01
> > is different
> > > to 1.1.1 and I need them to be grouped together in this query.
> > >
> > > In CF I could use
> > > numberformat(listfirst(location,"."),"00") & "." & ........
> > >
> > > To create the appropriate formatting but there are no such list
> > > functions in MySQL. Can anyone out there think of a way to do this?
> > >
> > > --
> > > James Smith
> > > [EMAIL PROTECTED]
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
