sql server data warehouse: I have the following table.


EXISTING TABLE:

COL1 COL2 COL3 MESSAGE

TOM CLERK VA TOM IS A GOOD CLERK. AGE:32,
SINCE:12/12/2001, PLAN:4
ROBERT INTERN DC ROBERT IS A FRESH RECRUIT.
AGE:21, SINCE:1/1/2003, PLAN:3
RICHARD STAFF VA RICHARD IS THE AD'S NEPHEW.
AGE:30, SINCE:12/12/2000, PLAN:2
MICHAEL MANAGER CA MICHAEL IS PROMOTED IN 2000.
AGE:35, SINCE:1/1/2001, PLAN:4
STEPHEN ASSTT NJ AGE:32, SINCE:6/1/2001, PLAN:4

JEFF CLERK IL JEFF IS ROBERT'S BROTHER. AGE:41,
SINCE:3/5/2003, PLAN:3



NEW TABLE

COL1 COL2 AGE SINCE PLAN
TOM CLERK 32 12/12/2001 4

ROBERT INTERN 21 1/1/2003 3

RICHARD STAFF 30 12/12/2000 2

MICHAEL MANAGER 35 1/1/2001 4
STEPHEN ASSTT 32 6/1/2001 4
JEFF CLERK 41 3/5/2003 3


HOW DO I EXTRACT THESE DIFFERENT PAIRS OF VALUES IN
DIFFERENT COLUMNS USING CHARINDEX/PATINDEX/STRING
FUNCTIONS OR ANY OTHER METHOD?


THANKS IN ADVANCE,

Rajesh

hi rajesh,

try query given in the following example which will extract 3 columns out of
message column.
you can add col1 and col2 to the table you have.

--sample data
create table tab1 (message varchar(500))
go
insert into tab1
select 'TOM IS A GOOD CLERK. AGE:32, SINCE:12/12/2001, PLAN:4' union all
select 'ROBERT IS A FRESH RECRUIT. AGE:21, SINCE:1/1/2003, PLAN:3' union
all
select 'RICHARD IS THE AD''S NEPHEW.AGE:30, SINCE:12/12/2000, PLAN:2' union
all
select 'MICHAEL IS PROMOTED IN 2000. AGE:35, SINCE:1/1/2001, PLAN:4' union
all
select 'AGE:32, SINCE:6/1/2001, PLAN:4' union all
select 'JEFF IS ROBERT''S BROTHER. AGE:41, SINCE:3/5/2003, PLAN:3'
go
--query.
select
substring(message, charindex ('AGE:', message) + 4,
charindex (',', message, charindex ('AGE:', message)) - (charindex ('AGE:',
message)+ 4) ) age,
substring(message, charindex ('SINCE:', message) + 6,
charindex (',', message, charindex ('SINCE:', message)) - (charindex
('SINCE:', message)+ 6) ) 'since',
right(message, charindex(':', reverse(message))-1) 'plan'
from tab1
go

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com