TRIMMIG IN MYSQL

Some time back I got stuck at the point in Mysql task ,I wanted to trip a part of already existing column in table and then insert into other column.

Reference is the column that contains the Reference number e.g. ‘090216-000016’
RDate is the column is which stores the date of the reference,  and first part of Reference contains the date. So I wanted to trip the Reference number to get the data for RDate column.

Here is how I did it,

mysql> select Reference from RNS  where Reference=’090216-000016′;
+—————+
| Reference     |
+—————+
| 090216-000016 |
+—————+
1 row in set (0.00 sec)

mysql> select LEFT (Reference,6) from RNS  where Reference=’090216-000016′;
+——————–+
| LEFT (Reference,6) |
+——————–+
| 090216             |
+——————–+
1 row in set (0.01 sec)

mysql> update RNS set RDATE=LEFT(Reference,6) where Reference=’090205-000154′;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select Reference,RDATE from RNS  where Reference=’090205-000154′;
+—————+————+
| Reference     | RDATE      |
+—————+————+
| 090205-000154 | 2009-02-05 |
+—————+————+

Hope it will be useful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s