Yeah.. I think i misunderstood what he is trying to do here.. I thought he only wanted rows where t BEGINS with /sr/db, but I think he wants rows that have a line in column t that begins with that -- then of those rows, he only wants t to contain the lines within t that begin with /sr/db as well.
Maybe? On Fri, Aug 21, 2009 at 10:13 AM, Jerry Schwartz <jschwa...@the-infoshop.com > wrote: > >-----Original Message----- > >From: Ananda Kumar [mailto:anan...@gmail.com] > >Sent: Friday, August 21, 2009 2:55 AM > >To: Johnny Withers > >Cc: MySQL > >Subject: Re: alternate in mysql > > > >Hi Johnny, > >Thanks for the reply, but the below sql does not seems to work > > > > SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; > >Empty set (0.02 sec) > > > > > >mysql> SELECT * FROM tmp WHERE t REGEXP '/sr/db/.*'; > >+----------------------------------------------------+ > >| t | > >+----------------------------------------------------+ > >| asdf > >/sr/db/ora/ora.ora > >/sr/db/ora/aaa.ora > >asdlkjf | > > > > > >On Thu, Aug 20, 2009 at 8:19 PM, Johnny Withers > <joh...@pixelated.net>wrote: > > > >> You could try: > >> > >> SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*'; > >> > >> On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar <anan...@gmail.com> > wrote: > >> > >>> Hi All, > >>> I have this data in both oracle and mysql. > >>> > >>> select * from tmp; > >>> T > >>> -------------------------------------------------- > >>> asdf > >>> /sr/db/ora/ora.ora > >>> asdfljk > >>> asdlkjf > >>> > >>> asdf > >>> /sr/db/ora/ora.ora > >>> /sr/db/ora/aaa.ora > >>> asdlkjf > >>> Where t is a varchar column, with each row having multiple lines. > >>> I can write this query in oracle to fetch only rows starting with > ''sr/db" > >>> > [JS] I might not understand what you want to do, especially since you begin > your string with two single-quotes and end it with one double-quote. > > You want to find those ROWS (not lines) in which `t` begins with the five > characters "s", "r", "/", "d", "b"? > > Would > > SELECT `t` FROM `tmp` WHERE `t` LIKE 'sr/db%'; > > do what you want? > > If you want to find those LINES which begin with the five characters, then > you have a much bigger headache. > > Regards, > > Jerry Schwartz > The Infoshop by Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > > > > > >>> select substr(t, instr(t, '/sr/db/'), instr(substr(t, instr(t, > '/sr/db/'), > >>> length(t)), chr(10))) from tmp; > >>> /sr/db/ora/ora.ora > >>> /sr/db/ora/ora.ora > >>> where chr(10) ..represents "NEW LINE" in oracle > >>> > >>> How do i do the same in mysql. > >>> > >>> Thanks for all you help. > >>> > >>> regards > >>> anandkl > >>> > >> > >> > >> > >> -- > >> ----------------------------- > >> Johnny Withers > >> 601.209.4985 > >> joh...@pixelated.net > >> > > > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
