Data conversion SQL Server 2005 Standard edition

[jpotucek]

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,t
ype,subArea,publishDate,expireDate,articleLanguage,indexImage,eventStartDat
e,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,NU
LL,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

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!

 

[SgtJarrow]

If all your Paths follow the same convention as the sample you provided, then the below should work. Not pretty code and maybe not the most efficient, but it does work based on the sample...

declare @value varchar(100)
declare @initialpath varchar(500)
declare @language varchar(50)
declare @namewithlanguage varchar(100)
declare @name varchar(100)

set @value = 'templatedata\Article\ColumbiaArticle\data\2002-01_wuerl_en'
--up to last slash
set @initialpath = SUBSTRING(@value, 1, LEN(@value) - CHARINDEX('\', REVERSE(@value), 1))
--language type
set @language = SUBSTRING(@value, LEN(@value) - CHARINDEX('_', REVERSE(@value), 1) + 2, LEN(@value))
--name with language
set @namewithlanguage = SUBSTRING(SUBSTRING(@value, LEN(@value) - CHARINDEX('\', REVERSE(@value), 1) + 2, LEN(@value)), 1, LEN(@value) - CHARINDEX('_', REVERSE(@value), 1) + 2)
--name
set @name = SUBSTRING(@namewithlanguage, 1, LEN(@namewithlanguage) - CHARINDEX('_', REVERSE(@namewithlanguage), 1))

select @value
		, @initialpath 
		, @language
		, @namewithlanguage
		, @name
		, @initialpath + '\' + @language + '\' + @name --new name

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[jpotucek]

Thanks

Guess I'm not understanding how this works..

I execute it and it returns one row but does not change anything..

Column 1,Column 2,Column 3,Column 4,Column 5,Column 6
templatedata\Article\ColumbiaArticle\data\2002-01_wuerl_en,templatedata
\Article\ColumbiaArticle
\data,en,2002-01_wuerl_en,2002-01_wuerl,templatedata\Article
\ColumbiaArticle\data\en\2002-01_wuerl

am I missing something?

 

[SgtJarrow]

What I provided was a framework for you to work from....it shows how to get each part of your required change and how to put it back together.

You will need to modify this into an update statement to run against you dbo.Article table. BEFORE YOU DO THIS MAKE SURE YOU HAVE A BACKUP OF YOUR TABLE!!! Can't stress that enough.

If you run into any trouble converting this over, let me know what you issues you are running into.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[jpotucek]

DUH!! I see now that I have to plug in my variables.. etc..

I will work on this and let you know how I make out