CODEWITHSUNDEEP
mysqloracle-database
schaturv
schaturv

Reputation: 122

Populating column based on existing columns in MySQL 5.6 and ORACLE 11 and ORACLE 12

I have a table where one column is of varchar type which contains value as '1.2.1', '1', '0.2' etc.

In general there will be max 3 numbers separated by decimal (.)

I need to create 3 new columns of Integer type to save these 3 numbers.

Sample data looks like this.

Sample data looks like this

Also, the data can contain some string value as well but its fixed that it will be one string appended at last, separated by hyphen (-). For Example 1.2.1-xyz. If the data is like this, the string value should go to fourth column.

This case data will look like below:

enter image description here

This is the query which I have written to achieve this requirement.

Please find below the SQLFiddle http://sqlfiddle.com/#!9/5aaeb4/1

Is there any better way of doing this? I am new to DB side so please suggest some optimized approach of achieving this.

Upvotes: 0

Views: 35

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

In MySQL, we can use Substring_Index() function as well as User-defined variables. Variables help us in avoiding redoing certain string operations again and again.

Schema (MySQL v5.7)

CREATE TABLE IF NOT EXISTS `table1` (
  `mainCol` varchar(45) NOT NULL 
) DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`mainCol`) VALUES
  ('1.2.1'),
  ('0.2'),
  ('1'), 
  ('1.2.1-xyz'),
  ('1.2');

Query

SELECT 
  mainCol, 

  @first := SUBSTRING_INDEX(mainCol, '.', 1) AS col1, 

  TRIM(LEADING '.' FROM 
       REPLACE(@second := SUBSTRING_INDEX(mainCol, '.', 2), 
               @first,
               '')
      ) AS col2,  

  @fourth := CASE WHEN INSTR(mainCol, '-') 
                  THEN SUBSTRING_INDEX(mainCol, '-', -1)
                  ELSE ''
             END AS col4, 

  REPLACE(TRIM(LEADING '.' FROM 
               REPLACE(SUBSTRING_INDEX(mainCol, '.', 3), 
                       @second,
                       '')
               ), 
          CONCAT('-', @fourth), 
          '') AS col3 
FROM table1
CROSS JOIN (SELECT @first := '', 
                   @second := '', 
                   @third := '',
                   @fourth := '') AS user_vars;

Result

| mainCol   | col1 | col2 | col4 | col3 |
| --------- | ---- | ---- | ---- | ---- |
| 1.2.1     | 1    | 2    |      | 1    |
| 0.2       | 0    | 2    |      |      |
| 1         | 1    |      |      |      |
| 1.2.1-xyz | 1    | 2    | xyz  | 1    |
| 1.2       | 1    | 2    |      |      |

View on DB Fiddle

Upvotes: 1

Related Questions