Replace .TIF with .pdf

Question

Post reply

Replace .TIF with .pdf

2Tall

SSChampion

Points: 14644
More actions
April 12, 2013 at 4:55 am

#276319

Hi to all. I want to update a column that links to an external document. The external documents have been converted from .TIF to .pdf. The file name remains the same.
UPDATE Documents
SET DocumentFileName = REPLACE(RIGHT(DocumentFileName,-3) 'TIF','pdf')
WHERE RIGHT(DocumentFileName,-3) ='TIF' AND DocumentLinked = '1'
[/Code]
The above is incorrect 🙂
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

2Tall SSChampion Points: 14644 More actions · Answer 1

OK, it looks like I was over thinking it.

[Code]

UPDATE Documents

SET DocumentFileName = REPLACE(DocumentFileName,'TIF','pdf')

WHERE DocumentFileName LIKE '%.TIF' AND DocumentLinked = '1'

[/Code]

Can anyone see any potential issued with above code?

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Mark Cowne One Orange Chip Points: 26968 More actions · Answer 2

2Tall (4/12/2013)
OK, it looks like I was over thinking it.
[Code]
UPDATE Documents
SET DocumentFileName = REPLACE(DocumentFileName,'TIF','pdf')
WHERE DocumentFileName LIKE '%.TIF' AND DocumentLinked = '1'
[/Code]
Can anyone see any potential issued with above code?
Kind Regards,
Phil.

That will change 'TIF' anywhere in DocumentFileName, try this instead

[Code]

UPDATE Documents

SET DocumentFileName = LEFT(DocumentFileName,LEN(DocumentFileName)-3) + 'pdf'

WHERE DocumentFileName LIKE '%.TIF' AND DocumentLinked = '1'

[/Code]

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Phil Parkin SSC Guru Points: 246991 More actions · Answer 3

I would refine your REPLACE slightly to include the '.' - this would help avoid the problem Mark mentioned.

update Documents

set DocumentFileName = replace(DocumentFileName, '.TIF', '.pdf')

where DocumentFileName like '%.TIF'

and DocumentLinked = '1'

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

2Tall SSChampion Points: 14644 More actions · Answer 4

Thanks for the responses. I will implement as suggesteed.

Phil

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

amandaallen243 Newbie Points: 5 More actions · Answer 5

2Tall (4/12/2013)
Hi to all. I want to update a column that links to an external document. The external documents have been converted from .TIF[/url] to .pdf[/url]. The file name remains the same.

Hey, how are things going? would you please post the correct code that you have implemented with Phil's advice? Thanks you.:-)

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 6

amandaallen243 (2/26/2014)
2Tall (4/12/2013)
Hi to all. I want to update a column that links to an external document. The external documents have been converted from .TIF[/url] to .pdf[/url]. The file name remains the same.
Hey, how are things going? would you please post the correct code that you have implemented with Phil's advice? Thanks you.:-)

Why? Don't you believe that Phil's code is correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

2Tall SSChampion Points: 14644 More actions · Answer 7

Hi. Phil's code is the way to go 🙂

Phil (the other one)

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper