convert() or cast() in valentina

[Giacomo Vernoni]

Update:
On Windows, the RegEx pattern doesn't work, but changing the "$" with "\Z" fixes it.
On Mac it works as expected in both ways.

So the query should be:

SELECT * FROM images WHERE img_name REGEX '^[^1-9]*10[^0-9]\Z*'



Giacomo


On 17 gen 2012, at 11:29, Giacomo Vernoni wrote:

> Just for the records, the solution to my problem is:
> 
> SELECT * FROM images WHERE img_name REGEX '^[^1-9]*10[^0-9]$*'
> 
> where "10" is the number to look for in the img_name field.
> 
> This query only finds names that contain the number 10 in the text (so "PIC_210_OK.jpg" will NOT be included in the results).
> 
> 
> For google: valentina convert string to number, valentina search exact number in string.
> 
> Giacomo
> 
> 
> On 17 gen 2012, at 10:08, Giacomo Vernoni wrote:
> 
>> I am aware of the existance of "LIKE"...
>> 
>> LIKE '%10%' will return ALL the records that CONTAIN "10", I need something that returns only the record that contains EXACTLY a number with the value of 10.
>> 
>> Anyway, I just discovered that CONVERT or CAST only work if the number is at the beginning of the field, so I'll have to find another solution.
>> 
>> I am trying with RegEx right now.
>> 
>> 
>> Giacomo
>> 
>> On 17 gen 2012, at 9:11, Ivan Smahin wrote:
>> 
>>> 
>>> On Jan 16, 2012, at 8:04 PM, Giacomo Vernoni wrote:
>>> 
>>>> Hi,
>>>> I know there's been a discussion back in 2008 about the SQL convert() function, but I need it for something different.
>>>> 
>>>> I have a "img_name" field with image names, for example:
>>>> 
>>>> IMG_0010.jpg
>>>> myPicture10a.jpg
>>>> PIC_210_OK.jpg
>>>> 
>>>> I need to search for the images that ONLY have the number 10.
>>>> In my example, I should get the first two records (that have a "value" of 10), excluding the last one that has a value of 210.
>>>> 
>>>> In MySQL I can easily do that with this query:
>>>> 
>>>> SELECT * FROM images WHERE CONVERT(img_name, SIGNED) = 10;
>>>> 
>>>> or using CAST:
>>>> 
>>>> SELECT * FROM images WHERE CAST(img_name as SIGNED) = 10;
>>>> 
>>>> 
>>>> How can I do this in Valentina?
>>>> 
>>> 
>>> 
>>> WHERE img_name LIKE '%10%;
>>> 
>>> -- 
>>> Best regards,
>>> Ivan Smahin
>>> Senior Software Engineer
>>> Paradigma Software, Inc
>>> Valentina - The Ultra-Fast Database
>>> http://www.valentina-db.com
>>> 
>>> _______________________________________________
>>> Valentina mailing list
>>> Valentina at lists.macserve.net
>>> http://lists.macserve.net/mailman/listinfo/valentina
>> 
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina