OraFAQ Forum: MySQL » getting output as per String content

Home » Open Source » MySQL » getting output as per String content (MySql)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

getting output as per String content [message #653639]

Wed, 13 July 2016 01:20

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

DB INPUT >> DB OUTPUT

DB INPUT

CODE
abc-1
,abd-1
abe-1
,xyz-1
,sef-1
,abw-1
qwe-1
,xsw-1
,ret-1

DB OUTPUT

CODE-A CODE-B
abc-1 abd-1
abe-1 xyz-1
abe-1 sef-1
abe-1 abw-1
qwe-1 xsw-1
qwe-1 ret-1

Here it checks for the string content with comma (,) and from single column in the input file append it to two columns in the output file.
the one which contains comma are appended in the second column, and the one without the output are appended in the first and act as the parent (as they are appended multiple times based on the comma.

Hope this is clear enough

Report message to a moderator

Re: getting output as per String content [message #653640 is a reply to message #653639]

Wed, 13 July 2016 01:26

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I've moved your post to the MySQL forum. You need to provide more detail: the CREATE TABLE statement and the INSERT statements needed to set up the problem.

Report message to a moderator

Re: getting output as per String content [message #653642 is a reply to message #653640]

Wed, 13 July 2016 02:26

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

I have a table XYZ with column code. Now I want to get column code_a and code_b as per the column code contents. (prefixed by comma).

A select statement will help.

select * from XYZ

CODE 
abc-1 
,abd-1 
abe-1 
,xyz-1
,sef-1 
,abw-1
qwe-1
,xsw-1
,ret-1

Now I want to write a select statement on the same table XYZ which will give me filtered output like this

CODE-A CODE-B 
abc-1 abd-1 
abe-1 xyz-1
abe-1 sef-1
abe-1 abw-1
qwe-1 xsw-1
qwe-1 ret-1

Report message to a moderator

Re: getting output as per String content [message #653643 is a reply to message #653642]

Wed, 13 July 2016 02:40

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

mysql> select a.code as code_a, b.code as code_b
    -> from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
    ->                code
    ->         from   mysql.xyz,
    ->                (select @rownuma := 0) r) a,
    ->       (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
    ->               code
    ->        from   mysql.xyz,
    ->               (select @rownumb := 0) r) b
    -> where a.code not like ',%'
    -> and   b.code like ',%'
    -> and   a.rn = b.rn;
+--------+--------+
| code_a | code_b |
+--------+--------+
| abc-1  | ,abd-1 |
| abe-1  | ,xyz-1 |
| abe-1  | ,sef-1 |
| abe-1  | ,abw-1 |
| qwe-1  | ,xsw-1 |
| qwe-1  | ,ret-1 |
+--------+--------+
6 rows in set (0.00 sec)

mysql>

Report message to a moderator

Re: getting output as per String content [message #653644 is a reply to message #653643]

Wed, 13 July 2016 02:52

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Thank you Barbara for the much needed help!

How to add other columns in the select statement here?

Report message to a moderator

Re: getting output as per String content [message #653646 is a reply to message #653644]

Wed, 13 July 2016 03:29

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Suppose you have another column named other_col like this:

mysql> select * from mysql.xyz;
+--------+-----------+
| code   | other_col |
+--------+-----------+
| abc-1  | something |
| ,abd-1 | nothing   |
| abe-1  | whatever  |
| ,xyz-1 | anything  |
| ,sef-1 | nonesense |
| ,abw-1 | scribble  |
| qwe-1  | another   |
| ,xsw-1 | and so on |
| ,ret-1 | etcetera  |
+--------+-----------+
9 rows in set (0.00 sec)

Then you can select that column like this and add any additional columns in the same manner:

mysql> select a.code as code_a, b.code as code_b, a.other_col, b.other_col
    -> from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
    ->                code,
    ->                other_col
    ->         from   mysql.xyz,
    ->                (select @rownuma := 0) r) a,
    ->       (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
    ->               code,
    ->               other_col
    ->        from   mysql.xyz,
    ->               (select @rownumb := 0) r) b
    -> where a.code not like ',%'
    -> and   b.code like ',%'
    -> and   a.rn = b.rn;
+--------+--------+-----------+-----------+
| code_a | code_b | other_col | other_col |
+--------+--------+-----------+-----------+
| abc-1  | ,abd-1 | something | nothing   |
| abe-1  | ,xyz-1 | whatever  | anything  |
| abe-1  | ,sef-1 | whatever  | nonesense |
| abe-1  | ,abw-1 | whatever  | scribble  |
| qwe-1  | ,xsw-1 | another   | and so on |
| qwe-1  | ,ret-1 | another   | etcetera  |
+--------+--------+-----------+-----------+
6 rows in set (0.00 sec)

Report message to a moderator

Re: getting output as per String content [message #653650 is a reply to message #653646]

Wed, 13 July 2016 04:22

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

yeah it worked,

Can i remove the prefixed comma in CODE-b column in the select query ??

Report message to a moderator

Re: getting output as per String content [message #653652 is a reply to message #653650]

Wed, 13 July 2016 04:57

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Sure, just use:

substr(b.code,2)

to select the substring of b.code, starting at the second character, as shown below.

mysql> select a.code as code_a, substr(b.code,2) as code_b, a.other_col, b.other_col
    -> from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
    ->                code,
    ->                other_col
    ->         from   mysql.xyz,
    ->                (select @rownuma := 0) r) a,
    ->       (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
    ->               code,
    ->               other_col
    ->        from   mysql.xyz,
    ->               (select @rownumb := 0) r) b
    -> where a.code not like ',%'
    -> and   b.code like ',%'
    -> and   a.rn = b.rn;
+--------+--------+-----------+-----------+
| code_a | code_b | other_col | other_col |
+--------+--------+-----------+-----------+
| abc-1  | abd-1  | something | nothing   |
| abe-1  | xyz-1  | whatever  | anything  |
| abe-1  | sef-1  | whatever  | nonesense |
| abe-1  | abw-1  | whatever  | scribble  |
| qwe-1  | xsw-1  | another   | and so on |
| qwe-1  | ret-1  | another   | etcetera  |
+--------+--------+-----------+-----------+
6 rows in set (0.00 sec)

Report message to a moderator

Re: getting output as per String content [message #653687 is a reply to message #653652]

Thu, 14 July 2016 11:39

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Hi barbara,

I am facing issue with the query you have provided.

The column code_b seems fine with the sequence provided from the input. but the column code_a is out of sequence. which is not as per the desired output.

I am using this query and for the table xyz i have a column id auto incremented for each rows.(primary key)

select a.code as code_a, substr(b.code,2) as code_b
from (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
code
from xyz,
(select @rownuma := 0) r) a,
(select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
code
from xyz,
(select @rownumb := 0) r) b
where a.code not like ',%'
and b.code like ',%'
and a.rn = b.rn;

Please help me out.

Report message to a moderator

Re: getting output as per String content [message #653688 is a reply to message #653652]

Thu, 14 July 2016 12:32

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

This is what i am getting right now with the query you have provided.

code-a---code-b---code-a_now
1----------x----------5
2----------y----------4
3----------z----------3
4----------p----------2

i want reverse of the code_a column

[Updated on: Thu, 14 July 2016 12:35]

Report message to a moderator

Re: getting output as per String content [message #653694 is a reply to message #653687]

Thu, 14 July 2016 18:10

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Quote:

...i have a column id auto incremented for each rows.(primary key)

It would have been nice to know that. You can use that column for ordering.

Quote:

This is what i am getting right now with the query you have provided.

code-a---code-b---code-a_now
1----------x----------5
2----------y----------4
3----------z----------3
4----------p----------2

Not with the data that you provided.

Quote:

i want reverse of the code_a column

Since your alleged output bears no relation to the data that you provided, I have no idea what you mean or what you really want.

You need to provide create table and insert statements and desired results based on the data that you provide. Please see the example that I have provided below.

If you have a create table statement like this:

create table mysql.xyz
  (id        integer auto_increment primary key, 
   code      char(6));

and you insert data like this:

insert into mysql.xyz (code) values ('abc-1');
insert into mysql.xyz (code) values (',abd-1');
insert into mysql.xyz (code) values ('abe-1');
insert into mysql.xyz (code) values (',xyz-1');
insert into mysql.xyz (code) values (',sef-1');
insert into mysql.xyz (code) values (',abw-1');
insert into mysql.xyz (code) values ('qwe-1');
insert into mysql.xyz (code) values (',xsw-1');
insert into mysql.xyz (code) values (',ret-1');

and you select like this, ordering by the id:

select * from mysql.xyz order by id;

then you get results in the same order of the original data that you posted:

+----+--------+
| id | code   |
+----+--------+
|  1 | abc-1  |
|  2 | ,abd-1 |
|  3 | abe-1  |
|  4 | ,xyz-1 |
|  5 | ,sef-1 |
|  6 | ,abw-1 |
|  7 | qwe-1  |
|  8 | ,xsw-1 |
|  9 | ,ret-1 |
+----+--------+
9 rows in set (0.00 sec)

If you run the query that you say you are running, adding ordering by the id's like this:

select a.code as code_a, substr(b.code,2) as code_b
from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
               code
        from   (select * from mysql.xyz order by id) xyz1,
               (select @rownuma := 0) r) a,
       (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
               code
        from   (select * from mysql.xyz order by id) xyz2,
               (select @rownumb := 0) r) b
where  a.code not like ',%'
and    b.code like ',%'
and    a.rn = b.rn;

then you get the results that you originally requested in the order that you originally requested like this:

+--------+--------+
| code_a | code_b |
+--------+--------+
| abc-1  | abd-1  |
| abe-1  | xyz-1  |
| abe-1  | sef-1  |
| abe-1  | abw-1  |
| qwe-1  | xsw-1  |
| qwe-1  | ret-1  |
+--------+--------+
6 rows in set (0.00 sec)

Is this still what you want or do you want something different? Is a different order a new requirement? You need to make your requirements clear. The results that you want must match the data that you provide.

Report message to a moderator

Re: getting output as per String content [message #653698 is a reply to message #653694]

Fri, 15 July 2016 00:30

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Hi Barbara,

Please have a look into this, the output is not as per the requirement, the column code_b is reverse the order. you can check the output of the query to get the issue.

1> Here is my select statement.

select * from master;

output

/forum/fa/13194/0/

2> Please find my query to filter

select a.code as code_a,substr(b.code,2) as code_b,a.id as id_a , b.id as id_b,
b.state,b.stock_unit,b.link_label,b.quantity_dms,b.revision,b.class_name
    from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
                  code,id
           from   master , 
                  (select @rownuma := 0) r) a, 
          (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
                  code,state,stock_unit,link_label,quantity_dms,revision,class_name,id
           from   master,
                  (select @rownumb := 0) r) b
    where a.code not like ',%'
    and   b.code like ',%'
    and   a.rn = b.rn order by id_a,id_b;

output

/forum/fa/13195/0/

[mod-edit: SelectStatement.JPG image moved to appropriate place by bb]

Attachment: SelectStatement.JPG
(Size: 33.79KB, Downloaded 5740 times)
Attachment: Output.JPG
(Size: 28.46KB, Downloaded 5714 times)

[Updated on: Fri, 15 July 2016 00:36]

Report message to a moderator

Re: getting output as per String content [message #653700 is a reply to message #653698]

Fri, 15 July 2016 01:21

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You still have not posted create table or insert statements, not even a copy and paste of a select that could be copied and pasted, just images.

Apparently you did not notice the order by clauses in the innermost sub-queries on the 5th and 9th lines. It is those that determine the ordering when calculating rownum for joining the tables.

-- test data:

mysql> select * from master order by id;
+----+----------------+----------+----------+--------------+------------+------------+---------------+
| id | code           | revision | state    | quantity_dms | link_label | stock_unit | class_name    |
+----+----------------+----------+----------+--------------+------------+------------+---------------+
|  1 | VSLVG5601-01   | 00       | APPROVED |         NULL |       NULL | EU         | STANDARD_PART |
|  2 | ,ST200-445-000 | 00       | APPROVED |            1 |         10 | AS_NEEDED  | STANDARD_PART |
|  3 | VSLVG4005-01   | 00       | APPROVED |         NULL |       NULL | EU         | STANDARD_PART |
|  4 | ,VSLVG4405-01  | 00       | APPROVED |            1 |         10 | EU         | STANDARD_PART |
|  5 | ,VSLVG4404-01  | 00       | APPROVED |            1 |         20 | EU         | STANDARD_PART |
|  6 | VSLVG3004-01   | 00       | APPROVED |         NULL |       NULL | EU         | STANDARD_PART |
|  7 | ,VSLVG3405-01  | 00       | APPROVED |            0 |         10 | EU         | STANDARD_PART |
+----+----------------+----------+----------+--------------+------------+------------+---------------+
7 rows in set (0.00 sec)

-- query:

mysql> select a.code as code_a, substr(b.code,2) as code_b, a.id as id_a, b.id as id_b,
    ->        b.state, b.stock_unit, b.link_label, b.quantity_dms, b.revision, b.class_name
    -> from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
    ->                code,id
    ->         from   (select * from master order by id) ma, 
    ->                (select @rownuma := 0) r) a, 
    ->        (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
    ->                code, state, stock_unit, link_label, quantity_dms, revision, class_name,id
    ->         from   (select * from master order by id) mb,
    ->                (select @rownumb := 0) r) b
    -> where a.code not like ',%'
    -> and   b.code like ',%'
    -> and   a.rn = b.rn 
    -> order by id_a, id_b;
+--------------+---------------+------+------+----------+------------+------------+--------------+----------+---------------+
| code_a       | code_b        | id_a | id_b | state    | stock_unit | link_label | quantity_dms | revision | class_name    |
+--------------+---------------+------+------+----------+------------+------------+--------------+----------+---------------+
| VSLVG5601-01 | ST200-445-000 |    1 |    2 | APPROVED | AS_NEEDED  |         10 |            1 | 00       | STANDARD_PART |
| VSLVG4005-01 | VSLVG4405-01  |    3 |    4 | APPROVED | EU         |         10 |            1 | 00       | STANDARD_PART |
| VSLVG4005-01 | VSLVG4404-01  |    3 |    5 | APPROVED | EU         |         20 |            1 | 00       | STANDARD_PART |
| VSLVG3004-01 | VSLVG3405-01  |    6 |    7 | APPROVED | EU         |         10 |            0 | 00       | STANDARD_PART |
+--------------+---------------+------+------+----------+------------+------------+--------------+----------+---------------+
4 rows in set (0.00 sec)

Report message to a moderator

Re: getting output as per String content [message #653702 is a reply to message #653700]

Fri, 15 July 2016 02:11

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Thanks Barbara for the help.
Will keep a note of providing detailed info including the create and insert statements from now onward.

Report message to a moderator

Re: getting output as per String content [message #654662 is a reply to message #653702]

Tue, 09 August 2016 01:06

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Hi,

I have another requirement which is as follows :-

It goes with the previous query provided by Barbara, Now one modification needs to be done.

This is my create statement, and the insert been done through ETL tool.

create table master(code varchar(20),revision varchar(5),state varchar(15), quantity_dms varchar(5),
link_label varchar(5),stock_unit varchar(10),class_name varchar(25));

CURRENTLY IT LOOKS LIKE THIS. with the select statement.

select a.code as code_a, substr(b.code,2) as code_b, a.id as id_a, b.id as id_b,
     b.state, b.stock_unit, b.link_label, b.quantity_dms, b.revision, b.class_name
    from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
                  code,id
            from   (select * from master order by id) ma, 
                    (select @rownuma := 0) r) a, 
           (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
                    code, state, stock_unit, link_label, quantity_dms, class_name,id,revision
             from   (select * from master order by id) mb,
                    (select @rownumb := 0) r) b
     where a.code not like ',%'
     and   b.code like ',%'
     and   a.rn = b.rn 
     order by id_a, id_b;

code_a | code_b | id_a | id_b | state | stock_unit | link_label | quantity_dms | revision | class_name |
+--------------+---------------+------+------+----------+------------+------------+--------------+----------+---------------+
| VSLVG5601-01 | ST200-445-000 | 1 | 2 | APPROVED | AS_NEEDED | 10 | 1 | 00 | STANDARD_PART |
| VSLVG4005-01 | VSLVG4405-01 | 3 | 4 | APPROVED | EU | 10 | 1 | 00 | STANDARD_PART |
| VSLVG4005-01 | VSLVG4404-01 | 3 | 5 | APPROVED | EU | 20 | 1 | 00 | STANDARD_PART |
| VSLVG3004-01 | VSLVG3405-01 | 6 | 7 | APPROVED | EU | 10 | 0 | 00 | STANDARD_PART |

now it has one modification, the Revision column will always map the parent revision. Parent are the ones without comma prefix, and they have multiple children(with comma). the parent revision will be the revision for all the child revision now onward.

mysql> select * from master order by id;
+----+----------------+----------+----------+--------------+------------+------------+---------------+
| id | code | revision | state | quantity_dms | link_label | stock_unit | class_name |
+----+----------------+----------+----------+--------------+------------+------------+---------------+
| 1 | VSLVG5601-01 | 00 | APPROVED | NULL | NULL | EU | STANDARD_PART |
| 2 | ,ST200-445-000 | 00 | APPROVED | 1 | 10 | AS_NEEDED | STANDARD_PART |
| 3 | VSLVG4005-01 | 00 | APPROVED | NULL | NULL | EU | STANDARD_PART |
| 4 | ,VSLVG4405-01 | 00 | APPROVED | 1 | 10 | EU | STANDARD_PART |
| 5 | ,VSLVG4404-01 | 00 | APPROVED | 1 | 20 | EU | STANDARD_PART |
| 6 | VSLVG3004-01 | 00 | APPROVED | NULL | NULL | EU | STANDARD_PART |
| 7 | ,VSLVG3405-01 | 00 | APPROVED | 0 | 10 | EU | STANDARD_PART |
+----+----------------+----------+----------+--------------+------------+------------+---------------+
7 rows in set (0.00 sec)

For example id 3 is parent and id 4 and 5 are children. the revision mapped with id 3 ( parent) will populate in the output throughout.

Hope this is clear.

Report message to a moderator

Re: getting output as per String content [message #654663 is a reply to message #654662]

Tue, 09 August 2016 01:20

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 14 July 2016 20:38

Please read How to use [code] tags and make your code easier to read.

chinmay45 wrote on Thu, 14 July 2016 21:04

Sure will use code tag now onwards

What about your promise?

Barbara Boehmer wrote on Fri, 15 July 2016 01:10

...
You need to provide create table and insert statements and desired results based on the data that you provide. Please see the example that I have provided below.
...

Barbara Boehmer wrote on Fri, 15 July 2016 08:21

You still have not posted create table or insert statements, not even a copy and paste of a select that could be copied and pasted, just images.

chinmay45 wrote on Fri, 15 July 2016 09:11

Thanks Barbara for the help.
Will keep a note of providing detailed info including the create and insert statements from now onward.

What about your promise?

Quote:

This is my create statement, and the insert been done through ETL tool.

And then? Do you think Barbara used your ETL in her previous posts to show the statements?
No, she wrote the INSERT statements, do the same thing and avoid her this work, it is yours.

[Updated on: Tue, 09 August 2016 01:21]

Report message to a moderator

Re: getting output as per String content [message #654666 is a reply to message #654663]

Tue, 09 August 2016 02:03

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

It is the same create and insert statement Barbara has used last time. So i skipped. Apology for that.

Report message to a moderator

Re: getting output as per String content [message #654668 is a reply to message #654666]

Tue, 09 August 2016 02:30

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As we, but Barbara, haven't them this means you want that only Barbara answers you, no problem for me, wait for her.

Report message to a moderator

Re: getting output as per String content [message #654672 is a reply to message #654668]

Tue, 09 August 2016 03:58

chinmay45
Messages: 15
Registered: July 2016
Location: bangalore

Junior Member

Hi Michel,Barbara.

I got the issue resolved . This is my query

select a.code as code_a, substr(b.code,2) as code_b, a.id as id_a, b.id as id_b,
     b.state, b.stock_unit, b.link_label, b.quantity_dms,a.revision, b.class_name
    from   (select @rownuma := IF(code not like ',%',@rownuma := @rownuma + 1, @rownuma := @rownuma) as rn,
                  code,id,revision
            from   (select * from master order by id) ma, 
                    (select @rownuma := 0) r) a, 
           (select @rownumb := IF(code not like ',%',@rownumb := @rownumb + 1, @rownumb := @rownumb) as rn,
                    code, state, stock_unit, link_label, quantity_dms, class_name,id
             from   (select * from master order by id) mb,
                    (select @rownumb := 0) r) b
     where a.code not like ',%'
     and   b.code like ',%'
     and   a.rn = b.rn 
     order by id_a, id_b;

Thanks for the help again.
Chinmaya

Report message to a moderator

Previous Topic:	getting output as per the column content
Next Topic:	Difference between DATETIME and TIMESTANP

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 21 20:24:55 CST 2024