Removing all but one of a certain character in a string-CodePudding

I have an issue where I'm trying to remove all of the '.' from the string/filename below in SSMS apart from the last one which dictates file type.

EPC 14.10.14.pdf

Ideally I would like this string to appear as below:

EPC 141014.pdf

Any help would be appreciated

CodePudding user response：

Use replace,substring and len function

select replace(substring(@x,0,len(@x) - 3),'.','')   substring(@x,len(@x) - 3,len(@x))

EDIT: If the name extension has a variable length, you can use the following query

select replace(substring(@x,0,len(@x) - CHARINDEX('.',REVERSE(@x))),'.','')
         
       substring(@x,len(@x) - CHARINDEX('.',REVERSE(@x)),len(@x))

Result

CodePudding user response：

declare @doc varchar(30) = 'EPC 14.10.14.pdf'

declare @ext varchar(8) = right(@doc, charindex('.', reverse(@doc)));
set @doc = concat(replace(left(@doc,len(@doc)-len(@ext)),'.',''), @ext);

select @doc as doc;

doc
EPC 141014.pdf

db<>fiddle here

CodePudding user response：

If you have extensions with different length (e.g. docx, xls), you need to find the index of the last occurrence of the . character using REVERSE() and CHARINDEX():

SELECT CONCAT( 
   REPLACE(SUBSTRING(SomeText, 1, LEN(SomeText) - CHARINDEX('.', REVERSE(SomeText))), '.', ''),
   STUFF(SomeText, 1, LEN(SomeText) - CHARINDEX('.', REVERSE(SomeText)), '')
) AS FileName
FROM (VALUES
   ('EPC 14.10.14.pdf'),
   ('EPC 14.10.14.docx'),
   ('14.10.14.xlsx')
) t (SomeText)

Result:

FileName
----------------
EPC 141014.pdf
EPC 141014.docx
141014.xlsx

CodePudding user response：

One more way.

SQL

SELECT fileName AS [Before]
    , CONCAT(CONCAT(PARSENAME(fileName,4), PARSENAME(fileName,3), PARSENAME(fileName,2))
    , '.', PARSENAME(fileName,1)) AS [After]
FROM (VALUES
   ('EPC 14.10.14.pdf'),
   ('EPC 14.10.14.docx'),
   ('14.10.14.xlsx'),
   ('csharp.10.14.cs')
) AS t(fileName);

Output

 ------------------- ----------------- 
|      Before       |      After      |
 ------------------- ----------------- 
| EPC 14.10.14.pdf  | EPC 141014.pdf  |
| EPC 14.10.14.docx | EPC 141014.docx |
| 14.10.14.xlsx     | 141014.xlsx     |
| csharp.10.14.cs   | csharp1014.cs   |
 ------------------- -----------------