remove substring after certain character if there is no number in it-CodePudding

I would like to remove a sub string after the last dot '.' if it does not contain a number.

Example data:

ID  Name    
1   example.jpg
2   exampleexample01.01.2014
3   example
4   example1.pdf
5   example13.pdf
6   this. is an. example
7   this.is.a.pdf

Desired result:

ID  Name    
1   example
2   exampleexample01.01.2014
3   example
4   example1
5   example13
6   this. is an. example
7   this.is.a

My solution which removes every sub string after a dot:

SELECT LEFT([Name], CHARINDEX('.', [Name]   '.') - 1 ) AS Name
FROM Table

Edit: I updated the example to show that there could be multiple dots in one string.

CodePudding user response：

This seems to do what you want; get the position of the last dot, check if those characters contain a number and if they do return Name. If not, string those characters from the end of the string:

SELECT *,
       CASE WHEN RIGHT(V.[Name],CI.LastDot) LIKE '%[0-9]%' THEN V.Name ELSE LEFT(V.[Name], LEN(V.Name) - CI.LastDot) END
FROM (VALUES(1,'example.jpg'),
            (2,'exampleexample01.01.2014'),
            (3,'example'),
            (4,'example1.pdf'),
            (5,'example13.pdf'))V(ID,Name)
     CROSS APPLY(VALUES(CHARINDEX('.',REVERSE(V.Name))))CI(LastDot);

CodePudding user response：

You can use this query. Basically, you find the string before & after the dot, and apply a simple case when based on your requirement.

WITH before_after AS (
SELECT Name,
LEFT([Name], CHARINDEX('.', [Name]   '.') - 1 ) AS before_dot,
SUBSTRING([Name], CHARINDEX('.', [Name])   1, LEN([Name]) - CHARINDEX('.', [Name])   1) AS after_dot
FROM table
)
SELECT CASE
    WHEN after_dot LIKE '%[0-9]%' THEN before_dot
    ELSE Name
    END Result
FROM before_after;

CodePudding user response：

try this, but please note that PARSENAME can be used only with less than or equal to 4 dots :

SELECT ID, CASE WHEN PARSENAME(REVERSE([Name]),1) LIKE '%[0-9]%' THEN [Name] 
        ELSE LEFT([Name], LEN([Name]) - CHARINDEX('.', REVERSE([Name]))) END AS [Name],
FROM [Table]

another way is:

SELECT ID, CASE WHEN RIGHT([Name], CHARINDEX('.', REVERSE([Name]))) LIKE '%.[0-9]%' THEN [Name] 
            ELSE LEFT([Name], LEN([Name]) - CHARINDEX('.', REVERSE([Name]))) END AS [Name]
FROM [Table]

CodePudding user response：

SELECT case when ISNUMERIC(RIGHT([Name], CHARINDEX('.', [Name]   '.') - 1 )) =1 then [Name]    
 else LEFT([Name], CHARINDEX('.', [Name]   '.') - 1 )  end as [Name] FROM Table