Home » Open Source » MySQL » getting output as per the column content (Mysql , Windows)
getting output as per the column content [message #653676] Thu, 14 July 2016 06:27 Go to next message
chinmay45
Messages: 15
Registered: July 2016
Location: bangalore
Junior Member
Hello,

I have a column code in table xyz which looks like this

select code from xyz;

code
---------------
abc
.abc
.abc
..abc
...abc
.abc

now i want to add one column level which counts the number of dots(.) prefixed.

level
---------
0
1
1
2
3
1

Once the LEVEL numbers are established, the 'dots'(...) preceding the values in the column CODE are removed.
so the column code will be without dots.


Hope this is clear enough.

Thanks
Chinmay
Re: getting output as per the column content [message #653677 is a reply to message #653676] Thu, 14 July 2016 06:50 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Chinmay, you have (again) placed your topic in the forum that is intended for Oracle SQL and PL/SQL. I shall (again) move it to the MySQL forum. I notice also that you have not followed my request to use [code] tags, or to provide the CREATE TABLE statement and the INSERT statements needed to set up the problem.

This behaviour is not going to endear you to other forum members.
Re: getting output as per the column content [message #653695 is a reply to message #653676] Thu, 14 July 2016 18:33 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
mysql> create table mysql.xyz
    ->   (code      char(6));
Query OK, 0 rows affected (0.28 sec)

mysql>
mysql> insert into mysql.xyz (code) values ('abc');
Query OK, 1 row affected (0.13 sec)

mysql> insert into mysql.xyz (code) values ('.abc');
Query OK, 1 row affected (0.09 sec)

mysql> insert into mysql.xyz (code) values ('.abc');
Query OK, 1 row affected (0.12 sec)

mysql> insert into mysql.xyz (code) values ('..abc');
Query OK, 1 row affected (0.07 sec)

mysql> insert into mysql.xyz (code) values ('...abc');
Query OK, 1 row affected (0.06 sec)

mysql> insert into mysql.xyz (code) values ('.abc');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> select * from mysql.xyz;
+--------+
| code   |
+--------+
| abc    |
| .abc   |
| .abc   |
| ..abc  |
| ...abc |
| .abc   |
+--------+
6 rows in set (0.00 sec)

mysql>
mysql> alter table mysql.xyz add level integer;
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> update mysql.xyz
    -> set    level = length(code)-length(replace(code,'.','')),
    ->        code = replace(code,'.','');
Query OK, 6 rows affected (0.04 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql>
mysql> select * from mysql.xyz;
+------+-------+
| code | level |
+------+-------+
| abc  |     0 |
| abc  |     1 |
| abc  |     1 |
| abc  |     2 |
| abc  |     3 |
| abc  |     1 |
+------+-------+
6 rows in set (0.00 sec)

mysql> 
Previous Topic: Names along with sum(sal)
Next Topic: getting output as per String content
Goto Forum:
  


Current Time: Thu Mar 28 05:27:33 CDT 2024