Select Right with CharIndex 1

[ptheriault]

I have a table named SI_REPORT_INFO with a column named REPORT_PATH. That path is the unc path to the report.
Currently the database looks like this.

\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\NAME.RTP
\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\ANOTHERNAME.RTP
\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\SOMENAME.RTP

It really should be
\\NEEASP\RPTS\NAME.RTP
\\NEEASP\RPTS\ANOTHERNAME.RTP
\\NEEASP\RPTS\SOMENAME.RTP

I thought this would give me just the report name and then I could just update the table. But I think I'm getting first '\' instead of the last '\' for my charindex.

SELECT RIGHT(REPORT_PATH, CHARINDEX('\', REPORT_PATH))
FROM SI_REPORT_INFO

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Does this help?

Declare @Temp Table(Data VarChar(1000))

Insert Into @Temp Values('\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\NAME.RTP')
Insert Into @Temp Values('\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\ANOTHERNAME.RTP')
Insert Into @Temp Values('\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\SOMENAME.RTP')

Select Right(data, CharIndex('\', Reverse(Data)))
From   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sunila7]

Will this not work?

SELECT REPLACE ('\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\NAME.RTP'
, 'e:\eas\server\STDRPTS9.0\', '')

Sunil

 

[SQLDenis]

why charindex just do a replace

create table bla12345 (Col varchar(100))
insert bla12345 
select '\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\NAME.RTP' union all
select '\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\ANOTHERNAME.RTP' union all
select '\\NEEASP\e:\eas\server\STDRPTS9.0\RPTS\SOMENAME.RTP'



select replace(col,'e:\eas\server\STDRPTS9.0\','')
from bla12345

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

George,
That is perfect. I hadn't heard of the reverse() function.

Here was my final update.

UPDATE SI_REPORT_INFO
SET REPORT_PATH = '\\NEEASP\RPTS'+Right(REPORT_PATH, CharIndex('\', Reverse(REPORT_PATH)))
FROM SI_REPORT_INFO

Thanks for the help!


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I didn't even think about replace!!

Darn!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Truth is... I did think about replace, but I was sure that ALL of the data in the table would have 'e:\eas\server\STDRPTS9.0\


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

It did. I'm installing a new accounting system for payables and all the crystal reports had that string in the path.
Thanks guys. It's fixed.

- Paul

- If at first you don't succeed, find out if the loser gets anything.