Getting rid of characters in a string

Question

Using Management Studio 2012, I have a field which holds paths to files

C:/Documents/FolderA/FolderA1/FileName1
C:/Documents/FolderA/FolderA1/FileName2
C:/Documents/FolderA/FolderA1/FileName3
C:/Documents/FolderA/FileNameA
C:/Documents/FolderA/FileNameB

Want my field to look like this

C:/Documents/FolderA/FolderA1/
C:/Documents/FolderA/FolderA1/
C:/Documents/FolderA/FolderA1/
C:/Documents/FolderA/
C:/Documents/FolderA/

Have tried using the function left but you have to declare the amount of characters to get to delete and obviously some file name are longer than others so this doesn't work.

This might be helpful https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server — apomene, Sep 20 '18 at 10:37

Yogesh Sharma · Accepted Answer · 2018-09-20T10:43:28.057

0

You can do :

select col, replace(col, right(col, charindex('/', reverse(col) + '/' ) -1), '')
from table t;

This assumes Folders always contains filenames.

edited Sep 20 '18 at 10:43

answered Sep 20 '18 at 10:38

Yogesh Sharma

47,987
5
21
48

Superb, thanks so much, never come across reverse before – Clem_Fandango Sep 20 '18 at 10:42
This is not a safe method, Imagine an imput like this: **C:/Files/Filename1/File** result: **C:/s/name1/** – t-clausen.dk Sep 20 '18 at 11:04
@t-clausen.dk. . . Read My bottom line. – Yogesh Sharma Sep 20 '18 at 11:08
Oh, you were referring to extension. I missed that. Anyway the link from the original question has an answer handling that using a safe method. – t-clausen.dk Sep 20 '18 at 11:14

Getting rid of characters in a string

1 Answers1