Let’s say you have a column on your table named name_title that contains an employee’s name and their title separated by a comma as such: ‘John Doe, Manager’.
The LEFT method will grab everything to the left of the comma ‘,’ and call it Name.
The REPLACE method will grab everything to the right of the comma ‘,’ and call it Title.
Your search results will contain two columns as such:
Name | Title ------------------ John Doe Manager
Here is the T-SQL below:
SELECT LEFT(name_title, CHARINDEX(',', name_title) - 1) AS Name, REPLACE(SUBSTRING(name_title, CHARINDEX(',', SystemVname_titlealue), LEN(name_title)), ',', '') AS Title
FROM name_table where key = 1