AccountId LoginId

1 Tata-MotorsHarry9

2 Orian-AnalyticsKartik10

3 adventure-worksRajashree3

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.

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.” for some reason.

Expected Output:

LoginId Name
Tata-MotorsHarry9 Harry
Orian-AnalyticsKartik10 Kartik
adventure-worksRajashree3 Rajashree