Trying to change substring. - 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

Trying to change substring.

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-24 : 04:43:31

I am trying to differentiate between a file name that ends with "001.sqb" and "_01.sqb". The following code doesn't make the distinction. I tried hard coding it without the @fileext variable.

SET @fileext = '_01.sqb'

IF @filequantity > '1'
BEGIN
IF @filename1 like '%\SQLBackup\Database\%'+@fileext+''
set @filename2 = substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype

IF @filequantity > '2'
BEGIN
set @filenum = (@FILENUM + '1')

set @filename3 = substring(@filename1,1,(len(@filename1)-5)) + cast(@filenum as varchar) + @filetype
END
ELSE
BEGIN
set @filename2 = substring(@filename1,1,(len(@filename1)-4)) +'_0' + cast(@filenum as varchar) + @filetype

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"!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 01:39:42

seems like what you need is CASE..WHEN rather than IF

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-25 : 02:01:52

I am getting a syntax error near the work CASE

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

IF @filequantity > '2'
BEGIN
set @filenum = (@FILENUM + '1')

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

END

ELSE
BEGIN
set @filename2 = substring(@filename1,1,(len(@filename1)-4)) + '_0' + cast(@filenum as varchar) + @filetype

IF @filequantity > '2'
BEGIN
set @filenum = (@FILENUM + '1')

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

END

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:05:46

it should be


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

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-25 : 02:14:03

That moves the syntax error to the next line:

IF @filequantity > '2'

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

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-25 : 02:17:38

u forgot to keep end after case statement
just keep end before IF @filequantity > '2'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:18:20

you need two ENDs one for CASE and other for BEGIN

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-25 : 02:32:14

This isn't working properly, still I have a file that ends with: "210001.sqb" and it is changing it to: "210002.sqb"

I need it to take when "@filename1 = 210001_01.sqb" and change it to "@filename2 = 210001_02.sqb" and when "@filename1 = 210001.sqb" and change it to "@filename2 = 210001_02.sqb"

It is working file for files that end with: 210001_01.sqb

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-25 : 02:39:04

just use

CASE WHEN CHARINDEX('_',column)>0 THEN REPLACE(column,'01.sqb','02.sqb') ELSE REPLACE(column,'.sqb','_02.sqb') END

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-25 : 02:46:32

The @filename variables are full paths that sometimes contain _ in other parts of the string.

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

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

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-25 : 03:01:47

Is there a way to say something like:

WHEN @filename1 = substring(@filename1,1,(len(@filename1)-6)= '_')

If it has the _ it will always be the 7th character from the end of the string. "_##.sqb"

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

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

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2008-12-25 : 03:26:56

It works:

SET @filenum = '2'

IF @filequantity > '1'
BEGIN
set @filename2 = CASE
--WHEN @filename1 like '%_01.sqb'
WHEN substring(@filename1,1,(len(@filename1)-7)) = '_'
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 @filenum = (@FILENUM + '1')

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

END
SET @filenum = '2'

BEGIN
set @filename2 = CASE
--WHEN @filename1 like '%_01.sqb'
WHEN substring(@filename1,1,(len(@filename1)-7)) != '_'
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 - 2008-12-25 : 03:34:27

Well...I was a bit premature with my declaration of success...

Now the files look like:
043120_01.sqb
043120_01_02.sqb
043120_01_03.sqb

or:

043120.sqb
043120_02.sqb
043120_03.sqb

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources