Remove first two characters and replace with a different string SQL Server

Question

From column Attachmentname I need to remove the first two characters and replace add a different string.

Select
    attachmentName
from 
    docstatus
where 
    salesorder =  '230162'


AttachmentName
-------------------------------
H:\Quality\4513014196_00040.pdf

I need to remove the H: and replace it with 'file://server/certs' so that the result is.

AttachmentName
-------------------------------------------------
file://server/certs/Quality/4513014196_00040.pdf

I can remove the first two characters but I don't know the string to add file://server/certs within the same string

right(AttachmentName, len(AttachmentName) - 2)

Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a tag for the database product you are using. Why should I tag my DBMS — user330315
– user330315, Commented Jun 15, 2021 at 20:00

Robert McKee · Accepted Answer · 2021-06-15 20:37:02Z

2

This doesn't quite do what you asked, but this is probably what you are looking for. It replaces the H:\ in a filename with file://server/certs/ and reverses the \ to / anywhere else. This makes the assumption that these are simple windows drive letter replacements attachment names, so H:\ can't really appear anywhere else other than at the beginning.

UPDATE docstatus
SET attachmentName = REPLACE(REPLACE(attachmentName, 'H:\', 'file://server/certs/'),'\','/')
WHERE salesorder = '230162'

another option that uses SUBSTR to remove the first 3 characters:

UPDATE docstatus
SET attachmentName = 'file://server/certs/' + REPLACE(SUBSTR(attachmentName, 4, LEN(attachmentName)),'\','/')
WHERE salesorder = '230162'
  AND attachmentName LIKE 'H:\%'

A third option, hinted at by Martin Smith:

UPDATE docstatus
SET attachmentName = REPLACE(STUFF(attachmentName, 1, 3, 'file://server/certs/'),'\','/')
WHERE salesorder = '230162'
  AND attachmentName LIKE '[A-Z]:\%'

edited Jun 15, 2021 at 20:37

answered Jun 15, 2021 at 20:08

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Carlos Over a year ago

Thank you Robert, the only problem with adding the H:\ to the query is that not all computers have the certs shared folder as letter H: which I have in my computer but it could be Y: at someone else's computer. this is why I will need to remove the first two characters and then insert file://server/certs

Robert McKee Over a year ago

Then use the second approach, but change the like to '[A-Z]:\%' this makes it so that if accidentally run multiple times, it won't try a replace again (since the like condition will fail the second time). Or you can remove that portion of it, and it will still work, but will make a mess of things if you accidentally run it again.

Carlos Over a year ago

Mark, I have a new problem, when the attachment field is empty, using AND attachment line '[A-Z]: \%' hides query results for items that dont have anything in that field.

Robert McKee Over a year ago

Sounds like you might be actually doing a SELECT instead of an UPDATE, in that case, remove the AND from the WHERE clause and try CASE WHEN in your SELECT, like:

SELECT CASE WHEN attachmentName LIKE '[A-Z]:\%' THEN REPLACE(STUFF(attachmentName, 1, 3, 'file://server/certs/'),'\','/') ELSE attachmentName END as attachmentName FROM docstatus WHERE salesorder = '230162'

Collectives™ on Stack Overflow

Remove first two characters and replace with a different string SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related