How to Parse Employee IDs Concatenated with Names Using SQL

In my database table, I had a field with values in the format of :

Employee Name (Employee ID)

And I needed to extract just the Employee ID.

For example, if the value was “Viktor Vektor (277)” then I wanted the output to be “277”.

I modeled this solution similarly to a previous post I wrote about for getting the filename (without an extension) in SQL.

The key fields are the first (to see what the sample data looks like) and the last.

Solution

SELECT
       myTable.employeeNameAndId
       , CHARINDEX('(', myTable.employeeNameAndId) AS positionOpenParenthesis
       , CHARINDEX(')', myTable.employeeNameAndId) AS positionCloseParenthesis 
       , CASE WHEN
                     CHARINDEX('(', myTable.employeeNameAndId) > 0
              AND CHARINDEX(')', myTable.employeeNameAndId) > 0
              THEN
                     SUBSTRING(myTable.employeeNameAndId
                           , CHARINDEX('(', myTable.employeeNameAndId) + 1
                           , CHARINDEX(')', myTable.employeeNameAndId) - CHARINDEX('(', myTable.employeeNameAndId) - 1
                     )
              ELSE
                     myTable.employeeNameAndId
       END AS employeeId
FROM dbo.myTable

Output

Explanation

I check if both the opening and a closing parenthesis are there.

• If both are present, the SUBSTRING function extracts the text between the parentheses.
• If either parenthesis is missing, it defaults to using the original value.

Key functions used:

• CHARINDEX: Finds the position of a specific character in a string.
• SUBSTRING: Extracts a portion of a string based on a starting position and length.