OraFAQ Forum: MySQL » getting output as per the column content

Home » Open Source » MySQL » getting output as per the column content (Mysql , Windows)

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

getting output as per the column content [message #653676]

Thu, 14 July 2016 06:27

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

Report message to a moderator

Re: getting output as per the column content [message #653677 is a reply to message #653676]

Thu, 14 July 2016 06:50

John Watson
Messages: 8989
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.

Report message to a moderator

Re: getting output as per the column content [message #653695 is a reply to message #653676]

Thu, 14 July 2016 18:33

Barbara Boehmer
Messages: 9106
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>

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Oct 30 21:03:17 CDT 2025