alternate in mysql

--000e0cd4871cd43144047193616d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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"

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

--000e0cd4871cd43144047193616d--

Re: alternate in mysql

--0016e6daa93d1fbc89047193dc3b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

You could try:

SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*';

On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar 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"
>
> 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
johnny@pixelated.net

--0016e6daa93d1fbc89047193dc3b--

Re: alternate in mysql

--000e0cd6b33682d0430471a1596a
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 wrote:

> You could try:
>
> SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*';
>
> On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar 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"
>>
>> 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
> johnny@pixelated.net
>

--000e0cd6b33682d0430471a1596a--

RE: alternate in mysql

>-----Original Message-----
>From: Ananda Kumar [mailto:anandkl@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
wrote:
>
>> You could try:
>>
>> SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*';
>>
>> On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar 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
>> johnny@pixelated.net
>>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: alternate in mysql

--0016364c7a513c203b0471a8a6d6
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 > wrote:

> >-----Original Message-----
> >From: Ananda Kumar [mailto:anandkl@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
> wrote:
> >
> >> You could try:
> >>
> >> SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*';
> >>
> >> On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar
> 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
> >> johnny@pixelated.net
> >>
>
>
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016364c7a513c203b0471a8a6d6--

Re: alternate in mysql

--000e0cd5f7ee388e7d0472f7b45d
Content-Type: text/plain; charset=ISO-8859-1

Hi All,
Thanks a lot for all your help.
I was able to do it using "sed" os command.
I spooled the data from db into a file and used the sed command on it.

thanks once again.

regards
anandkl

On Fri, Aug 21, 2009 at 9:07 PM, Johnny Withers wrote:

> 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 <
> jschwartz@the-infoshop.com> wrote:
>
>> >-----Original Message-----
>> >From: Ananda Kumar [mailto:anandkl@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
>> wrote:
>> >
>> >> You could try:
>> >>
>> >> SELECT * FROM tmp WHERE t REGEXP '^/sr/db/.*';
>> >>
>> >> On Thu, Aug 20, 2009 at 9:15 AM, Ananda Kumar
>> 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
>> >> johnny@pixelated.net
>> >>
>>
>>
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--000e0cd5f7ee388e7d0472f7b45d--