Topic: Removing part of character of filename MSSQL

I asked this question last week but a little bit different, unfortunately I do not have a good answer, I tested all the suggestions, unfortunately it did not work. Today I'll show you what kind of request I wrote.

   

 SELECT 
       (CASE WHEN displayname LIKE '%~%' THEN 
               rtrim(left(displayname,charindex('~',displayname)-1)) 
               ELSE displayname 
       END) AS displayname 
    FROM Description

The problem in this request is I can remove the string '~ DEL ~ xxxxxx' where x is a number between (0-9) unfortunately the file type will also be removed, maybe someone here could help me.

If I make the request

SELECT [displayname]
FROM [dbo]. [Description]

Current file names:

|                 DisplayName                    |
|------------------------------------------------|
| KM_003__Rheihe 4~DEL~389948.pdf                |
| KM_004__Reite 2 und Reite 3~DEL~391098.pdf     |
| KM_008__Grundriss~DEL~391042.pdf               |
| KM_006__Breite~DEL~398646.zip                  |
| KM_005_Hoch~DEL~356477.dwg                     |
| SAM_0440~DEL~3088.JPG                          |
|              ...                               |
|              ...                               |
|              ...                               |
|              ...                               |

What I want it to be:

|                DisplayName                     |
|------------------------------------------------|
| KM_003__Rheihe 4.pdf                           |
| KM_004__Reite 2 und Reite 3.pdf                |
| KM_008__Grundriss.pdf                          |
| KM_006__Breite.zip                             |
| KM_005_Hoch.dwg                                |
| SAM_0440.JPG                                   |
|              ...                               |
|              ...                               |
|              ...                               |

If I make my request, I get that

|                DisplayName                     |
|------------------------------------------------|
| KM_003__Rheihe 4                               |
| KM_004__Reite 2 und Reite 3                    |
| KM_008__Grundriss                              |
| KM_006__Breite                                 |
| KM_005_Hoch                                    |
| SAM_0440                                       |
|              ...                               |
|              ...                               |
|              ...                               |

If you look correctly, it already fulfils its task in part, unfortunately, the content type (.pdf, .dwg, .zip, etc) has also been removed.

Post's attachments

Attachment icon remotedesktop.png 79.04 kb, 175 downloads since 2019-11-25 

2 (edited by derek 2019-11-26 15:13:19)

Re: Removing part of character of filename MSSQL

Hello Henri,
Please have a look at the attachment for one way of doing it but there are probably other (better!) ways (just couldn't think of any!).
Hope this helps,
Derek.

Post's attachments

Attachment icon filename.zip 337.47 kb, 440 downloads since 2019-11-26 

Re: Removing part of character of filename MSSQL

Here is another option. If your file extensions are always 3 such as pdf, zip, doc...etc, you can append the following to your rtrim statement in your SELECT.


|| SUBSTR(displayname, Length(displayname) - 3, 4)

Re: Removing part of character of filename MSSQL

Are you using Sqlite or MySQL?

Re: Removing part of character of filename MSSQL

Thank you Guys,

ich have the solution

UPDATE Description
SET displayname =
(CASE WHEN displayname LIKE '%~%' AND displayname LIKE '%.%' THEN rtrim(left(displayname,charindex('~',displayname)-1)) 
+ right(displayname,charindex('.',reverse(displayname))) ELSE displayname END)