Re: [sqlite] select with a like containing a line feed

[Sylvain Pointeau]

it is really perfect,thank you so much

On Fri, Apr 3, 2009 at 2:55 PM, Simon Davies <
simon.james.dav...@googlemail.com> wrote:

> 2009/4/3 Sylvain Pointeau :
> > re-hi,
> > but how do we do if we are on unix and there is some CR on those field?
> > I cannot insert CR in my statement (only line feed)
> >
> > do you have any suggestion?
> >
> > Cheers,
> > Sylvain
> >
>
> Something like this:
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite>  create table tst( id integer primary key, data text );
> sqlite>
> sqlite> insert into tst( data ) values( '123
>   ...> 456' );
> sqlite>
> sqlite> insert into tst( data ) values( 'abc' );
> sqlite>
> sqlite> select * from tst where data like '%'||X'0A'||'%';
> 1|123
> 456
> sqlite> select * from tst where data not like '%'||X'0A'||'%';
> 2|abc
> sqlite>
>
>
> Rgds,
> Simon
> ___
> 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] select with a like containing a line feed

[Simon Davies]

2009/4/3 Sylvain Pointeau :
> re-hi,
> but how do we do if we are on unix and there is some CR on those field?
> I cannot insert CR in my statement (only line feed)
>
> do you have any suggestion?
>
> Cheers,
> Sylvain
>

Something like this:

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite>  create table tst( id integer primary key, data text );
sqlite>
sqlite> insert into tst( data ) values( '123
   ...> 456' );
sqlite>
sqlite> insert into tst( data ) values( 'abc' );
sqlite>
sqlite> select * from tst where data like '%'||X'0A'||'%';
1|123
456
sqlite> select * from tst where data not like '%'||X'0A'||'%';
2|abc
sqlite>


Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select with a like containing a line feed

[Sylvain Pointeau]

re-hi,
but how do we do if we are on unix and there is some CR on those field?
I cannot insert CR in my statement (only line feed)

do you have any suggestion?

Cheers,
Sylvain

On Fri, Apr 3, 2009 at 2:15 PM, Sylvain Pointeau  wrote:

> Hi,
> thank you very much for your answers,
>
> I didn't think to put the real line feed into my shell script command line.
>
> Cheers,
> Sylvain
>
>
> On Fri, Apr 3, 2009 at 1:35 PM, Emil Obermayr  wrote:
>
>> On Fri, Apr 03, 2009 at 12:25:18PM +0200, Sylvain Pointeau wrote:
>> >
>> > How do I specify the character "line feed" (\n)
>> > in my query?
>>
>> Depends a little on your wrapper (outer) programming language.
>>
>> A technic that works in most language is to fill a variable with the
>> code of LF and then use this variable to concatenate the string. Even
>> the \n stuff should work in many languages.
>>
>> Tell us your coding language to get more specific hints.
>> ___
>> 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] select with a like containing a line feed

[Emil Obermayr]

On Fri, Apr 03, 2009 at 12:25:18PM +0200, Sylvain Pointeau wrote:
> 
> How do I specify the character "line feed" (\n)
> in my query?

Depends a little on your wrapper (outer) programming language.

A technic that works in most language is to fill a variable with the
code of LF and then use this variable to concatenate the string. Even
the \n stuff should work in many languages. 

Tell us your coding language to get more specific hints.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select with a like containing a line feed

[Igor Tandetnik]

"Sylvain Pointeau"
 wrote in
message
news:af5c8a920904030325w631f8dfcu90f5f80266f57...@mail.gmail.com
> I have some line feed in one field in my table
> and I would like to replace this line feed by a space.
>
> How do I specify the character "line feed" (\n)
> in my query?
>
> ex: select * from mytable where myfield like '%\n%'

Just literally enter a line feed:

select * from mytable where myfield like '%
%'

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users