Re: [Bacula-users] Filename searching and case insensitivity
I think I understand but as the Filename column in the Bacula script is created with the BLOB data type then according to the documentation the lower() function does not work: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_lower It says I need to convert it into a non-binary string, I don't really have any SQL skills at all but I'll see what I can do :) On 9 April 2011 16:01, Dan Langille wrote: > > On Apr 5, 2011, at 10:19 PM, Kernel Panic wrote: > >> On 6 April 2011 02:37, Dan Langille wrote: >>> >>> On Apr 5, 2011, at 9:31 PM, Kernel Panic wrote: >>> Hello everyone, Whilst trying to find a way of doing a case-insensitive search for file, I found previous posts on the mailing lists that instructed me to use the sqlquery function in bacula. As a test I wanted to search for files with zfs in their name and then with ZFS in the name. After starting up bconsole and entering sqlquery mode I did the following: USE bacula; SELECT * FROM Filename WHERE name LIKE '%ZFS%'; SELECT * FROM Filename WHERE name LIKE '%zfs%'; Although the commands worked, they only returned case-sensitive matches, despite MySQL's documentation stating that pattern matching is case-insensitive by default: http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html Can anyone help me? >>> >>> Have you tried ilike instead of like? It is available on PostgreSQL. I >>> don't know about MySQL. >>> >>> Consider also: >>> >>> SELECT * FROM Filename where lower(name) LIKE %zfs%; >> >> Unfortunately ilike/ILIKE do not seem to be valid operators in MySQL. >> The lower(name) doesn't cause a problem but the results are still >> case-sensitive > > Think wider. > > It is not case insensitive. It is all lower case. You are comparing lower > case to lower case. > > Does that help? -- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Filename searching and case insensitivity
On Apr 5, 2011, at 10:19 PM, Kernel Panic wrote: > On 6 April 2011 02:37, Dan Langille wrote: >> >> On Apr 5, 2011, at 9:31 PM, Kernel Panic wrote: >> >>> Hello everyone, >>> >>> Whilst trying to find a way of doing a case-insensitive search for >>> file, I found previous posts on the mailing lists that instructed me >>> to use the sqlquery function in bacula. As a test I wanted to search >>> for files with zfs in their name and then with ZFS in the name. After >>> starting up bconsole and entering sqlquery mode I did the following: >>> >>> USE bacula; >>> SELECT * FROM Filename WHERE name LIKE '%ZFS%'; >>> SELECT * FROM Filename WHERE name LIKE '%zfs%'; >>> >>> Although the commands worked, they only returned case-sensitive >>> matches, despite MySQL's documentation stating that pattern matching >>> is case-insensitive by default: >>> http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html >>> >>> Can anyone help me? >> >> Have you tried ilike instead of like? It is available on PostgreSQL. I >> don't know about MySQL. >> >> Consider also: >> >> SELECT * FROM Filename where lower(name) LIKE %zfs%; > > Unfortunately ilike/ILIKE do not seem to be valid operators in MySQL. > The lower(name) doesn't cause a problem but the results are still > case-sensitive Think wider. It is not case insensitive. It is all lower case. You are comparing lower case to lower case. Does that help? -- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Filename searching and case insensitivity
On 6 Apr 2011, at 02:37, Dan Langille wrote: > > On Apr 5, 2011, at 9:31 PM, Kernel Panic wrote: >> >> USE bacula; >> SELECT * FROM Filename WHERE name LIKE '%ZFS%'; >> SELECT * FROM Filename WHERE name LIKE '%zfs%'; >> >> Although the commands worked, they only returned case-sensitive >> matches, despite MySQL's documentation stating that pattern matching >> is case-insensitive by default: >> http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html >> >> Can anyone help me? > > Have you tried ilike instead of like? It is available on PostgreSQL. I > don't know about MySQL. > > Consider also: > > SELECT * FROM Filename where lower(name) LIKE %zfs%; > - In Postgres a case insensitive regex match can be done like this: name ~* 'zfs'-- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Filename searching and case insensitivity
On 6 April 2011 02:31, Kernel Panic wrote: > Hello everyone, > > Whilst trying to find a way of doing a case-insensitive search for > file, I found previous posts on the mailing lists that instructed me > to use the sqlquery function in bacula. As a test I wanted to search > for files with zfs in their name and then with ZFS in the name. After > starting up bconsole and entering sqlquery mode I did the following: > > USE bacula; > SELECT * FROM Filename WHERE name LIKE '%ZFS%'; > SELECT * FROM Filename WHERE name LIKE '%zfs%'; > > Although the commands worked, they only returned case-sensitive > matches, despite MySQL's documentation stating that pattern matching > is case-insensitive by default: > http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html > > Can anyone help me? > > Thanks. > Looking at the code in the make_mysql_tables script shows the following: -- Note, we use BLOB rather than TEXT because in MySQL, -- BLOBs are identical to TEXT except that BLOB is case -- sensitive in sorts, which is what we want, and TEXT -- is case insensitive. -- CREATE TABLE Filename ( FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Name BLOB NOT NULL, PRIMARY KEY(FilenameId), INDEX (Name(255)) ); I admit I know next to nothing about SQL, but since they are using BLOB when creating the 'Filename' table does this mean a case-insensitive search is not possible? -- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Filename searching and case insensitivity
On 6 April 2011 02:37, Dan Langille wrote: > > On Apr 5, 2011, at 9:31 PM, Kernel Panic wrote: > >> Hello everyone, >> >> Whilst trying to find a way of doing a case-insensitive search for >> file, I found previous posts on the mailing lists that instructed me >> to use the sqlquery function in bacula. As a test I wanted to search >> for files with zfs in their name and then with ZFS in the name. After >> starting up bconsole and entering sqlquery mode I did the following: >> >> USE bacula; >> SELECT * FROM Filename WHERE name LIKE '%ZFS%'; >> SELECT * FROM Filename WHERE name LIKE '%zfs%'; >> >> Although the commands worked, they only returned case-sensitive >> matches, despite MySQL's documentation stating that pattern matching >> is case-insensitive by default: >> http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html >> >> Can anyone help me? > > Have you tried ilike instead of like? It is available on PostgreSQL. I > don't know about MySQL. > > Consider also: > > SELECT * FROM Filename where lower(name) LIKE %zfs%; Unfortunately ilike/ILIKE do not seem to be valid operators in MySQL. The lower(name) doesn't cause a problem but the results are still case-sensitive -- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Filename searching and case insensitivity
On Apr 5, 2011, at 9:31 PM, Kernel Panic wrote: > Hello everyone, > > Whilst trying to find a way of doing a case-insensitive search for > file, I found previous posts on the mailing lists that instructed me > to use the sqlquery function in bacula. As a test I wanted to search > for files with zfs in their name and then with ZFS in the name. After > starting up bconsole and entering sqlquery mode I did the following: > > USE bacula; > SELECT * FROM Filename WHERE name LIKE '%ZFS%'; > SELECT * FROM Filename WHERE name LIKE '%zfs%'; > > Although the commands worked, they only returned case-sensitive > matches, despite MySQL's documentation stating that pattern matching > is case-insensitive by default: > http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html > > Can anyone help me? Have you tried ilike instead of like? It is available on PostgreSQL. I don't know about MySQL. Consider also: SELECT * FROM Filename where lower(name) LIKE %zfs%; -- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Filename searching and case insensitivity
Hello everyone, Whilst trying to find a way of doing a case-insensitive search for file, I found previous posts on the mailing lists that instructed me to use the sqlquery function in bacula. As a test I wanted to search for files with zfs in their name and then with ZFS in the name. After starting up bconsole and entering sqlquery mode I did the following: USE bacula; SELECT * FROM Filename WHERE name LIKE '%ZFS%'; SELECT * FROM Filename WHERE name LIKE '%zfs%'; Although the commands worked, they only returned case-sensitive matches, despite MySQL's documentation stating that pattern matching is case-insensitive by default: http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html Can anyone help me? Thanks. -- Xperia(TM) PLAY It's a major breakthrough. An authentic gaming smartphone on the nation's most reliable network. And it wants your games. http://p.sf.net/sfu/verizon-sfdev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
