valentina 1.1 sql questions

[Ruslan Zasukhin]

On 11/8/06 11:28 PM, "dave" <dcaruso3 at verizon.net> wrote:

Hi Dave,

> hello again
>  
> a few more questions about sql searches
> i changed my search string to use single quotes and use the "like" instead of
> "where ="
> now i have a few new problems, we are using this to search a database of mp3
> files
> the file names contain some characters that seem to cause problems
>  example:
>  
> a record has a field named filename, refering to the actual file name of the
> mp3 file
>  
> the mp3 name is "(Everything I Do) I Do It For / Leann Rimes"
> so i try to do a serach like this
  
> select * from bluesalbum where filename like '(?i)(Everything I Do) I Do It
> For / Leann Rimes'
>  
> i assume this does not work because of the ( ) in the string?

Right, because () are special chars of REGEX, so you need ESCAPE them.

select * 
from bluesalbum 
where filename like '(?i)\(Everything I Do\) I Do It For / Leann Rimes'

If you produce this SQL query using some TYPING of a user, then you can
automate this process using ValentinaEscapeString() function.

  
> i have tried using [ ] insted of single quotes but that causes other problems
> also have strings that start with or end with a single quote, that causes
> problems
> and have strings with ( ) and [ ] in them also, more problems
> so i guess what i am asking is how do i handle these items in my search
> strings?
>  
> i did try using something like this also
>  
> select * from bluesalbum where filename like '(Everything I Do) I Do It For /
> Leann Rimes' no_case
> but still have problems.

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]