Something is not right... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Something is not right...

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-13 : 05:55:36

The following code takes these files:

dw_FULL_20090113_023031_01.sqb
FULL_20090112_210001.sqb

and turns them into:
dw_FULL_20090113_023031_01_02.sqb
FULL_20090112_210001_02.sqb

IF @filequantity > '1'
BEGIN
set @filename2 = CASE
WHEN substring(@filename1,1,(len(@filename1)-7)) like '%_01.sqb'
THEN substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype
--set @filename2 = substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype
end
IF @filequantity > '2'
BEGIN

set @filename3 = substring(@filename2,1,(len(@filename2)-5)) + cast((@filenum +1) as varchar) + @filetype

END

ELSE

SET @filenum = '2'

BEGIN
set @filename2 = CASE
WHEN substring(@filename1,1,(len(@filename1)-7)) not like '%_01.sqb'
THEN substring(@filename1,1,(len(@filename1)-5)) + '_0' + cast(@filenum as varchar) + @filetype
END
IF @filequantity > '2'
BEGIN
set @filenum = (@FILENUM + '1')

set @filename3 = substring(@filename2,1,(len(@filename2)-4)) + cast(@filenum as varchar) + @filetype
END
END
END

The second example is correct but it is skipping the first part all together and runs the second part on all files...

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

Working until "the morning sun sets the midnight sky on fire"!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 06:18:30

Problem at:
WHEN substring(@filename1,1,(len(@filename1)-7)) like '%_01.sqb'
a SELECT substring(@filename1,1,(len(@filename1)-7)) gives 'dw_FULL_20090113_023031'

replace that line with:
WHEN Right(@filename1,7) like '%_01.sqb'

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-13 : 06:45:32

This leaves @filename2 and @filename3 blank.

I also tried changing the -7 to -1 and '%_01.sq' but that left both blank as well.

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

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-13 : 06:57:00

I also tried simplifying it to:

set @filename2 = CASE
WHEN @filename1 like '%_01.sqb'
THEN substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype

set @filename2 = CASE
WHEN @filename1 not like '%_01.sqb'
THEN substring(@filename1,1,(len(@filename1)-4)) + '_0' + cast(@filenum as varchar) + @filetype

Still blank.

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

Working until "the morning sun sets the midnight sky on fire"!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 09:57:36

This works for me:
declare @filename1 varchar(100)
declare @filename2 varchar(100)
set @filename1 = 'dw_FULL_20090113_023031_01.sqb'
set @filename2 = 'FULL_20090112_210001.sqb'

declare @filetype varchar(6),@filenum int
set @filenum=2
set @filetype = '.sqb'

set @filename2 = CASE
WHEN @filename1 like '%_01.sqb'
THEN substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype
end
select @filename2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 10:23:06

wont this be enough?


SELECT REPLACE(@filename1,'_01.sqb','_01_02.sqb'),REPLACE(@filename2,'_01.sqb','_01_02.sqb')

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-13 : 10:51:39

Sorry, both of the file names are correct variations of @filename1:
dw_FULL_20090113_023031_01.sqb
FULL_20090112_210001.sqb

The correct form for @filename2 would be:
dw_FULL_20090113_023031_02.sqb
FULL_20090112_210001_02.sqb

I can alter the code so that the first file is renamed correctly, but I end up with the second file looking like:
FULL_20090112_210002.sqb

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

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 11:32:19

SELECT REPLACE(REPLACE(filename1,'_01.sqb','_01_02.sqb'),'01.sqb','_02.sqb')

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-14 : 01:59:08

set @filename2 = SELECT REPLACE(REPLACE(@filename1,'_01.sqb','_01_02.sqb'),'01.sqb','_02.sqb')

Gives a syntax error at SELECT

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

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 02:14:45

it should be

SELECT @filename2 = REPLACE(REPLACE(@filename1,'_01.sqb','_01_02.sqb'),'01.sqb','_02.sqb')

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-14 : 02:21:59

I got it to work.... "ELSE" was creating the problem...

IF @filequantity > '1'

BEGIN
set @filename2 = CASE
WHEN substring(@filename1,(len(@filename1)-6),1) = '_'
THEN substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype
end
IF @filequantity > '2'
BEGIN
--print 'greater than 2'
set @filename3 = substring(@filename2,1,(len(@filename2)-5)) + cast((@filenum +1) as varchar) + @filetype
END

IF @filequantity > '1' and substring(@filename1,(len(@filename1)-6),1) != '_'
BEGIN
SET @filenum = '2'
set @filename2 = CASE
WHEN substring(@filename1,(len(@filename1)-7), 7) not like '%_01.sqb'
THEN substring(@filename1,1,(len(@filename1)-4)) + '_0' + cast(@filenum as varchar) + @filetype
END
IF @filequantity > '2'
BEGIN
set @filenum = (@FILENUM + '1')

set @filename3 = substring(@filename2,1,(len(@filename2)-5)) + cast(@filenum as varchar) + @filetype
END
END
END

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

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-14 : 02:29:51

Thanks for they help, guys...

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

Working until "the morning sun sets the midnight sky on fire"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 02:49:54

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources