1 Reply by Henri

(4 replies, posted in SQL queries)

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)

Go to forum: SQL queries

2 Topic by Henri

(4 replies, posted in SQL queries)

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.

Go to forum: SQL queries