SQL for Data Conversion

• Jpotucek

  SSCoach

  Points: 18583

  More actions

  April 7, 2010 at 6:39 am

  #233485

  Question for the SQL gurus....

  I'm researching a Data Conversion.

  I've included the Table DDL and some sample Data (current data and expected results data)

  The PATH column is the Primary Key.

  Example Contents of Path column

  templatedata\Article\ColumbiaArticle\data\2004-09_Spalding_en

  Need to change to

  templatedata\Article\ColumbiaArticle\data\en\2004-09_Spalding

  Table DDL

  Create table dbo.Article (

  id int identity,

  IW_State VARCHAR(255) not null,

  path VARCHAR(255) not null,

  contentArea CHAR(10) not null,

  homepage CHAR(5) null,

  title NVARCHAR(400) null,

  summary NVARCHAR(1000) null,

  keywords NVARCHAR(50) not null,

  author NVARCHAR(50) null,

  type CHAR(10) not null,

  subArea CHAR(10) null,

  publishDate datetime not null,

  expireDate datetime not null,

  articleLanguage CHAR(5) not null,

  indexImage VARCHAR(255) null,

  eventStartDate datetime null,

  eventEndDate datetime null,

  eventLocation NVARCHAR(50) null,

  agentID CHAR(10) null,

  ccText ntext null,

  indexImageCaption NVARCHAR(100) null) ;

  --------------------------------------------------

  -- Create Primary Key PK_Article

  --------------------------------------------------

  alter table dbo.Article

  add constraint PK_Article

  primary key (path);

  Sample Data (current)

  id,IW_State,path,contentArea,homepage,title,summary,keywords,author,type,subArea,publishDate,expireDate,articleLanguage,indexImage,eventStartDate,eventEndDate,eventLocation,agentID,ccText,indexImageCaption

  244,"Original","templatedata\Article\ColumbiaArticle\data\2002-01_wuerl_en","COL",NULL,"Reflection and Guidance","The first in a new series of columns on Pope John Paul II's encyclicals and other major teaching documents.","encyclicals, wuerl","Bishop Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL

  241,"Original","templatedata\Article\ColumbiaArticle\data\2002-01_wuerl_es","COL",NULL,"Reflección y Guía","La primera de una serie de columnas acerca de las encíclas y de otros documentos didácticos del Papa Juan Pablo II.","wuerl, papa, juan pablo","Obispo Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","es",NULL,NULL,NULL,NULL,NULL,NULL,NULL

  245,"Original","templatedata\Article\ColumbiaArticle\data\2002-02_wuerl_en","COL",NULL,"Focus on the Future","In his first encyclical, Pope John Paul II studied the human condition and set the stage for the sweeping scope of his pontificate.","wuerl, pontificate","Bishop Donald Wuerl","COL","AUTH01","2002-02-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL

  Sample Data (Expected results)

  id,IW_State,path,contentArea,homepage,title,summary,keywords,author,type,subArea,publishDate,expireDate,articleLanguage,indexImage,eventStartDate,eventEndDate,eventLocation,agentID,ccText,indexImageCaption

  244,"Original","templatedata\Article\ColumbiaArticle\data\en\2002-01_wuerl","COL",NULL,"Reflection and Guidance","The first in a new series of columns on Pope John Paul II's encyclicals and other major teaching documents.","encyclicals, wuerl","Bishop Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL

  241,"Original","templatedata\Article\ColumbiaArticle\data\es\2002-01_wuerl","COL",NULL,"Reflección y Guía","La primera de una serie de columnas acerca de las encíclas y de otros documentos didácticos del Papa Juan Pablo II.","wuerl, papa, juan pablo","Obispo Donald Wuerl","COL","AUTH01","2002-01-01","9999-01-01","es",NULL,NULL,NULL,NULL,NULL,NULL,NULL

  245,"Original","templatedata\Article\ColumbiaArticle\data\en\2002-02_wuerl","COL",NULL,"Focus on the Future","In his first encyclical, Pope John Paul II studied the human condition and set the stage for the sweeping scope of his pontificate.","wuerl, pontificate","Bishop Donald Wuerl","COL","AUTH01","2002-02-01","9999-01-01","en",NULL,NULL,NULL,NULL,NULL,NULL,NULL

  This one of a couple of tables that I am looking at for the conversion project... about 4000 rows in this table..

  Any Help or advice would be greatly appreciated!

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  April 7, 2010 at 9:30 am

  #1147447

  First thing you need to look at are tghe followin functions

  REPLACE

  SUBSTRING

  CHARINDEX

  LEN

  RIGHT

  LEFT

  Example Contents of Path column

  templatedata\Article\ColumbiaArticle\data\2004-09_Spalding_en

  Need to change to

  templatedata\Article\ColumbiaArticle\data\en\2004-09_Spalding

  i'll do it in stages

  first thing is to find the last 2 characters of the path

  select right(2,path) from ...

  then you need to add this into the path - so mergeing this ito my update it would be

  update ... set path=replace(path,'\Data\','\Data\'+ right(2,path)+'\')

  now you need to chop off the end 3 charachters use the len function to determine it's length

  select len(path)

  so len(path)-3 goes into your left function

  so using the left function the entire syntax would be

  update ... set path=left(len(path)-3, replace(path,'\Data\','\Data\'+ right(2,path)+'\' )) where ...

  an important thng to do here is not apply this to anything with len(path)<3 as it will error due to the negative value passed into the left function

  hope this helps a bit.... - i'm sure it can be improved, but it will get you started

  MVDBA