Help with a small SQL problem

[button71]

I have

SELECT * FROM PTEMP ;
		INTO TABLE pictures ;
		ORDER  BY Fname

I wish to exclude all files except jpg , bmp , tif, gif etc

Can anyone help?

Thanks

William

 

[mm0000]

Try the following code. Fieldname in the query represents the field containing the names of the files.

SELECT * FROM PTEMP ;
        INTO TABLE pictures ;
        where justext(fieldname) $ 'jpg/bmp/tif/gif' ;        
ORDER  BY Fname

 

[Mike Lewis]

MM0000,

where justext(fieldname) $ 'jpg/bmp/tif/gif'

As William wants to exclude the specified extensions, you need a NOT in there.

Also, it would be a good idea to wrap the JUSTEXT(fieldname) in a LOWER(), to avoid any issues with case.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[mm0000]

Mike,

Button71 wrote :

I wish to exclude all files except jpg , bmp , tif, gif etc

I think he wants to exclude all files other than image files from the query. In other words only image files are to be selected by the query.

Yes, wrapping the statement with LOWER() is required to avoid case issues, the query can be changed to

SELECT * FROM PTEMP ;
        INTO TABLE pictures ;
        where lower(justext(fieldname)) $ 'jpg/bmp/tif/gif' ;        
ORDER  BY Fname

 

[Mike Lewis]

mm0000,

You're right. My mistake. I was caught by the double negative.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk