Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replace .TIF with .pdf Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 4:55 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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'

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
Post #1441633
Posted Friday, April 12, 2013 5:05 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
OK, it looks like I was over thinking it.

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

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

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
Post #1441638
Posted Friday, April 12, 2013 5:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
2Tall (4/12/2013)
OK, it looks like I was over thinking it.

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

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

Can anyone see any potential issued with above code?
Kind Regards,
Phil.


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

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

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




____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1441642
Posted Friday, April 12, 2013 5:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:49 PM
Points: 4,973, Visits: 11,660
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'




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1441646
Posted Friday, April 12, 2013 6:09 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #1441659
Posted Wednesday, February 26, 2014 7:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 7:25 PM
Points: 1, Visits: 1
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 to .pdf. 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.
Post #1545665
Posted Wednesday, February 26, 2014 9:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:31 AM
Points: 36,714, Visits: 31,164
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 to .pdf. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1545683
Posted Thursday, February 27, 2014 1:22 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 5:29 AM
Points: 621, Visits: 1,073
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
Post #1545750
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse