SQL Script

• vijay.aproju

  Newbie

  Points: 9

  More actions

  February 6, 2015 at 9:24 am

  #315071

  create table t1(id number, description varchar2(25));

  Insert table t1(id, description) values(101,'10000;01/02/2015');

  Insert table t1(id, description) values(102,'10003;21/01/2015');

  Insert table t1(id, description) values(103,'01/02/2015;10002');

  Insert table t1(id, description) values(104,'13/01/2015;10004');

  Like that we have data, now we want to separate the description column one is date and another one is number.

  If possible plz build any one new script the below output and let me know. thanks.......

  Output:

  iddescriptiondatenumber

  10110000;01/02/20151/2/201510000

  10210003;21/01/201521/01/201510003

  10301/02/2015;100021/2/201510002

  10413/01/2015;1000413/01/201510004

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 6, 2015 at 11:47 am

  #1776136

  I'm guessing that you're working with Oracle because is the one that has varchar2 (AFAIK).

  This is a SQL Server forum which means that you might not get the best suggestions for other RDBMS. This is a script tested on Oracle that might help you.

  SELECT id,

  description,

  TO_DATE( CASE WHEN col1 LIKE '%/%' THEN col1 ELSE col2 END) somedate,

  CASE WHEN col1 LIKE '%/%' THEN col2 ELSE col1 END somenumber

  FROM(

  SELECT t1.*,

  SUBSTR( description, 1, INSTR( description, ';') - 1) col1,

  SUBSTR( description, INSTR(description, ';') + 1, 25) col2

  FROM t1

  )x;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2