Re: [sqlite] Concat two fields for LIKE query?
P Kishor wrote: > 2009/6/4 "Andrés G. Aragoneses" <kno...@gmail.com>: >> Igor Tandetnik wrote: >>> "Andrés G. Aragoneses" <kno...@gmail.com> >>> wrote: >>>> My query, which I want to make it return the first row: >>>> >>>> SELECT * FROM SomeTable WHERE Path+FileName LIKE '%user/File%' >>> SELECT * FROM SomeTable WHERE Path || FileName LIKE '%user/File%'; >>> >>> In SQL, string concatenation operator is ||, not +. >>> >> Cool! And can I do this as well?: >> >> SELECT Path||Filename FROM SomeTable WHERE Path || FileName LIKE >> '%user/File%' >> > > Sure, but wouldn't it be easier and quicker for you to just try it > yourself than ask? Don't be afraid. Try it out. SELECTs can't harm > your database. Thanks, I had to run and didn't have time to test it at that moment :P Andres -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concat two fields for LIKE query?
Igor Tandetnik wrote: > "Andrés G. Aragoneses" <kno...@gmail.com> > wrote: >> My query, which I want to make it return the first row: >> >> SELECT * FROM SomeTable WHERE Path+FileName LIKE '%user/File%' > > SELECT * FROM SomeTable WHERE Path || FileName LIKE '%user/File%'; > > In SQL, string concatenation operator is ||, not +. > Cool! And can I do this as well?: SELECT Path||Filename FROM SomeTable WHERE Path || FileName LIKE '%user/File%' Thanks, Andrés -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concat two fields for LIKE query?
Schema: CREATE TABLE SomeTable ( ID INTEGER NOT NULL PRIMARY KEY, Path TEXT, FileName TEXT ) Content (2 rows): 1 /home/user/ File.mp3 2 /home/user/M/ File.mp3 My query, which I want to make it return the first row: SELECT * FROM SomeTable WHERE Path+FileName LIKE '%user/File%' Is there any way to do this? Thanks! Andrés -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2 columns as primary key?
Thanks! It worked. Sorry for the silly question :) Igor Tandetnik wrote: > ""Andrés G. Aragoneses"" > <kno...@gmail.com> wrote in message > news:gvq7b2$lp...@ger.gmane.org >> CREATE TABLE LastSyncedRatings ( >> DapID TEXT PRIMARY KEY, >> MetadataHashTEXT PRIMARY KEY, >> Rating INTEGER NOT NULL >> ) > > Make it > > CREATE TABLE LastSyncedRatings ( > DapID TEXT , > MetadataHashTEXT, > Rating INTEGER NOT NULL, > PRIMARY KEY(DapID, MetadataHash) > ); > > Igor Tandetnik > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2 columns as primary key?
CREATE TABLE LastSyncedRatings ( DapID TEXT PRIMARY KEY, MetadataHashTEXT PRIMARY KEY, Rating INTEGER NOT NULL ) Pavel Ivanov wrote: > What create table statement did you use? > > Pavel > > 2009/5/29 "Andrés G. Aragoneses" <kno...@gmail.com>: >> I just tried to create a primary key with 2 columns and got this error: >> >> "sqlite error" "table X has more than one primary key" >> >> >> Doesn't SQLite support this?? :o >> >>Andres >> >> -- >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] 2 columns as primary key?
I just tried to create a primary key with 2 columns and got this error: "sqlite error" "table X has more than one primary key" Doesn't SQLite support this?? :o Andres -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Igor Tandetnik wrote:
> "Andrés G. Aragoneses" <[EMAIL PROTECTED]>
> wrote:
>> Igor Tandetnik wrote:
>>> "Andrés G. Aragoneses"
>>> <[EMAIL PROTECTED]> wrote:
>>>> Andrés G. Aragoneses wrote:
>>>>> Igor Tandetnik wrote:
>>>>>> "Andrés G. Aragoneses"
>>>>>> <[EMAIL PROTECTED]> wrote:
>>>>>>> Hello, I am querying a list of elements and some of them are
>>>>>>> blank (''). However, I want that those who are blank appear as
>>>>>>> the last (instead of the first) elements when I sort with ORDER
>>>>>>> BY alphabetically. How can I do this with just SQL?
>>>>>> ORDER BY text = '', text
>>>>> It works thanks!
>>>> Hugh, but this seems not to be applicable for different columns,
>>>> because, let's suppose I have:
>>>>
>>>> ItemId ItemName ItemNameSort
>>>> 1'Balloon' 'balloon'
>>>> 2'' '[noname]'
>>>> 3'Car' 'car'
>>>>
>>>> If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I
>>>> want to get 1,3,2. Any ideas?
>>> What's ItemNameLowered? Your table doesn't seem to have such a
>>> column.
>>>
>>> Have you tested it? I don't see how row 2 could appear first. You
>>> don't actually have apostrophes stored in the database as part of
>>> your data, do you? What does the following statement output:
>>>
>>> select ItemId, ItemName, length(ItemName)
>>> from myTable;
>> Sorry, a typo: not ItemNameLowered but ItemNameSort. When I use '' is
>> to differentiate it from the int type (which I used for the key). So
>> let's rewrite the question:
>>
>> ItemId ItemName ItemNameSort
>> 1'Balloon' 'balloon'
>> 2'' '[noname]'
>> 3'Car' 'car'
>>
>> If I use "ORDER BY ItemName = '', ItemNameSort ASC" I get 2,1,3 and I
>> want to get 1,3,2. Any ideas?
>
> I tested it, and I get 1, 3, 2. I can reproduce your result only if I
> put NULL into ItemName, rather than an empty string. What does this
> statement return:
You're right, I had nulls instead of empty strings.
> select ItemId, ItemName, typeof(ItemName)
> from myTable
>
> If you do need to handle NULLs, simply change the condition to
>
> ORDER BY ifnull(ItemName, '')='', ItemNameSort ASC
>
Thanks!
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Igor Tandetnik wrote:
> "Andrés G. Aragoneses" <[EMAIL PROTECTED]>
> wrote:
>> Andrés G. Aragoneses wrote:
>>> Igor Tandetnik wrote:
>>>> "Andrés G. Aragoneses"
>>>> <[EMAIL PROTECTED]> wrote:
>>>>> Hello, I am querying a list of elements and some of them are blank
>>>>> (''). However, I want that those who are blank appear as the last
>>>>> (instead of the first) elements when I sort with ORDER BY
>>>>> alphabetically. How can I do this with just SQL?
>>>> ORDER BY text = '', text
>>> It works thanks!
>> Hugh, but this seems not to be applicable for different columns,
>> because, let's suppose I have:
>>
>> ItemId ItemName ItemNameSort
>> 1'Balloon' 'balloon'
>> 2'' '[noname]'
>> 3'Car' 'car'
>>
>> If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I
>> want to get 1,3,2. Any ideas?
>
> What's ItemNameLowered? Your table doesn't seem to have such a column.
>
> Have you tested it? I don't see how row 2 could appear first. You don't
> actually have apostrophes stored in the database as part of your data,
> do you? What does the following statement output:
>
> select ItemId, ItemName, length(ItemName)
> from myTable;
Sorry, a typo: not ItemNameLowered but ItemNameSort. When I use '' is to
differentiate it from the int type (which I used for the key). So let's
rewrite the question:
ItemId ItemName ItemNameSort
1'Balloon' 'balloon'
2'' '[noname]'
3'Car' 'car'
If I use "ORDER BY ItemName = '', ItemNameSort ASC" I get 2,1,3 and I
want to get 1,3,2. Any ideas?
Thanks again,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Petite Abeille wrote: > On May 16, 2008, at 10:23 PM, Andrés G. Aragoneses wrote: > >> If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I >> want to get 1,3,2. Any ideas? > > Perhaps something like: > > select * > from item > order by case > when name = '' then 'z' > else name >end > Interesting, but the replacement to 'z' seems kind of a hack, I would not prefer magic strings... Anyway thanks for your idea! Andres -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Andrés G. Aragoneses wrote:
> Igor Tandetnik wrote:
>> "Andrés G. Aragoneses" <[EMAIL PROTECTED]>
>> wrote:
>>> Hello, I am querying a list of elements and some of them are blank
>>> (''). However, I want that those who are blank appear as the last
>>> (instead of the first) elements when I sort with ORDER BY
>>> alphabetically. How can I do this with just SQL?
>> ORDER BY text = '', text
>
> It works thanks!
Hugh, but this seems not to be applicable for different columns,
because, let's suppose I have:
ItemId ItemName ItemNameSort
1'Balloon' 'balloon'
2'' '[noname]'
3'Car' 'car'
If I use "ORDER BY ItemName = '', ItemNameLowered" I get 2,1,3 and I
want to get 1,3,2. Any ideas?
Regards,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exceptions to sorting?
Igor Tandetnik wrote:
> "Andrés G. Aragoneses" <[EMAIL PROTECTED]>
> wrote:
>> Hello, I am querying a list of elements and some of them are blank
>> (''). However, I want that those who are blank appear as the last
>> (instead of the first) elements when I sort with ORDER BY
>> alphabetically. How can I do this with just SQL?
>
> ORDER BY text = '', text
It works thanks! I've also noticed that every non-alphabetical character
is also ordered before alphabetical ones, how can I invert this behaviour?
Regards,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Exceptions to sorting?
Hello, I am querying a list of elements and some of them are blank ('').
However, I want that those who are blank appear as the last (instead of
the first) elements when I sort with ORDER BY alphabetically. How can I
do this with just SQL?
Thanks,
Andres
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
