Dynamically remove the numbers from the end of a varchar in SQL

Question

AccountId        LoginId
1                Tata-Motors\Harry9  
2                Orian-Analytics\Kartik10  
3                adventure-works\Rajashree3

I am working on Microsoft SQL Server 2022 - 16.0.1115.1
I have this kind of sample data I created for this experiment. I want to remove the company name from the front and the numbers from the back dynamically. I have figured out removing the company name quite easily but I am struggling with dynamically removing the number from the end such that the program works regardless of the length of the number.

Create statement:

CREATE TABLE Tester(
    [BusinessEntityID] [int] NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL
)

Insert Statement:

insert into dbo.Tester(BusinessEntityID, LoginId)
values
(1, 'Tata-Motors\Harry9'),
(2, 'Orian-Analytics\Kartik10'),
(3,'adventure-works\Darshana3')

I tried PatIndex but I don't seem to be able to figure out the exact formula to solve this problem.

select LoginId,
    Substring(LoginId,
    charindex('\', LoginID)+1, LEN(LoginID)-charindex('\', LoginID)-patindex('%[0-9]%',LoginId)) Name
from dbo.Tester

This is what I have come up with so far but it is giving an error

Invalid length parameter passed to the LEFT or SUBSTRING function.

Expected Output:

LoginId                     Name
Tata-Motors\Harry9          Harry
Orian-Analytics\Kartik10    Kartik
adventure-works\Rajashree3  Rajashree

Answer 1

A minimal reproducible example is not provided.

I am assuming it is SQL Server 2022.

In SQL Server 2022, an enhancement is available in the TRIM() function. This enhancement allows to remove any specific character(s) from one of the sides (left, right) or both sides of the column \ expression along with the space character char(32).

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (AccountId INT IDENTITY PRIMARY KEY, LoginId VARCHAR(128));
INSERT @tbl (LoginId) VALUES
('Tata-Motors\Harry9'),
('Orian-Analytics\Kartik10'),
('adventure-works\Rajashree3');
-- DDL and sample data population, end

-- Method #1
SELECT * 
    , TRIM(TRAILING '1234567890' FROM RIGHT(LoginId, LEN(LoginId) - CHARINDEX('\', LoginId + '\'))) AS [name]
FROM @tbl;

-- Method #2
SELECT * 
    , TRIM(TRAILING '1234567890' FROM PARSENAME(REPLACE(LoginId,'\','.'), 1)) AS [name]
FROM @tbl;

Output

AccountId	LoginId	name
1	Tata-Motors\Harry9	Harry
2	Orian-Analytics\Kartik10	Kartik
3	adventure-works\Rajashree3	Rajashree