question on using SQL string functions

[Deane Venske]

On Wed, 14 Jan 2004 20:19:44 -0700, Dave Parizek <dave at Parizek.com> wrote:

>>> I am running the following query from within Valentina Studio:
>>>
>>> UPDATE Item set storageloc = LEFT(Sku, (locate(Sku, '-')-1))
>>>
>>> Sku and storageloc are fields of the table.  Sku's structure is 
>>> something like this: '1032-10200'
>>>
>>> I would expect storageloc to become '1032'
>>>
>>> but instead it becomes 'LEFT'
>>>
>>> What am I doing wrong?
>>>
>>> --Dave
>>
>> Hi Dave,
>>
>> I'm pretty sure this wont be able to do this until version 2.0 is 
>> ready. I'm looking forward to this too, being able to use functions 
>> inside of queries.
>>
>
> If you can't do them inside of queries, can you use them some other 
> way?  It seems strange that they are documented in the sql pdf if you 
> can't use them yet.  Kind of almost false advertising...
>
> --Dave
>

They can be used in queries, but only in this context :

SELECT LEFT('1032-10200', (locate('1032-10200', '-')-1))
Which will return the result. It cannot be used in conjunctions with 
lookups to check actual values in the database. I often find I need to 
combine data or rip it apart like you've done. Some times I like adding 
things to the data, like for monetary values I like doing "SELECT '$' 
+ Cost FROM items", but alas until v2.0 we can't do this.

2 point 0, 2 point 0, 2 point 0, 2 point 0, 2 point 0, 2 point 0, 2 point 
0, 2 point 0, 2 point 0, 2 point 0!!!

-- 
Deane Venske - Senior Programmer
www.eduss.com.au   deane.venske at eduss.com
+61 7 55974600  +61 405148718
Suite 29,  207 Currumburra Rd
Ashmore, Queensland, 4214
Australia

Notice
This message and any files transmitted may contain privileged and 
confidential information and is intended only for the use of the addressee 
or entity named above.  If you are not the intended recipient, please 
notify Eduss Limited immediately by return email or telephone 07 5597 4600 
(International +61 7 55974600) and delete all copies of this message with 
any attachments.  Any dissemination, distribution or copying of this email 
is strictly prohibited.
Eduss Limited, disclaims liability for any loss, damage or expense however 
caused, arising from the sending, receipt, or use of this email and on any 
reliance placed upon the information provided through this service and 
does not guarantee the completeness or accuracy of the information. Eduss 
does not represent or warrant the attached files are free from computer 
viruses or other defects.  The attached files are provided, and may only 
be used, on the basis that the user assumes all responsibility for any 
loss, damage or consequence resulting directly or indirectly from use.
Any views expressed in this message are those of the original sender, 
except where the sender specifically states them to be the views of Eduss 
Limited.