Do you have to do this with an expression? I ask, as it may be easier to return the parts in your dataset, rather than splitting them in the report. For example:
WITH Users AS(
SELECT *
FROM (VALUES ('sabdbsdlkfbaskl f\Steve Jobs'),
('asdkbgaksdaskbdk\Jane-Smith'),
('kasjdgbasdvajklsvd\Craig.Charles'),
('sdfgasdjlfgalsdhfsajldf\Jennifer_Lopez'),
('sdiafgaskjdkjhobkjah\Bill/Gates')) V (UserID)),
Names AS(
SELECT U.UserID,
RIGHT(U.UserID, CHARINDEX('\',REVERSE(U.UserID))-1) AS FullName
FROM Users U)
SELECT *,
LEFT(FullName, PATINDEX('%[/ \._-]%',FullName)-1) AS FirstName,
RIGHT(FullName, PATINDEX('%[/ \._-]%',REVERSE(FullName))-1) AS Surname
FROM Names N;
You don't need to do with a CTE, but just easier to see what it's doing, IMO.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk