Extracting text before hyphen in SQL: 'XYZ-789' to 'XYZ'

Data and Databases
1

I’ve got a database table called EMPLOYEE with columns ID and NAME_CODE. Some NAME_CODE entries look like this:

NAME_CODE
---------
Johnson-123
Emma
Chris-45
Olivia
---------

I want to show these values without the hyphen and numbers after it, like this:

NAME_CODE
---------
Johnson
Emma
Chris
Olivia
---------

How can I get rid of everything after (and including) the hyphen? I tried this query but it’s not working right:

SELECT ID, NAME_CODE, LEFT(NAME_CODE, CHARINDEX('-', NAME_CODE + '-') - 1) AS cleaned_name
FROM EMPLOYEE

Can someone help me fix this or suggest a better way? Thanks!

2

Ooh, interesting problem! have u considered using PARSENAME? it’s pretty nifty for splitting strings. something like this might work:

SELECT ID, NAME_CODE,
PARSENAME(REPLACE(NAME_CODE, ‘-’, ‘.’), 2) AS cleaned_name
FROM EMPLOYEE

what do you think? it could be a fun alternative to try out :slight_smile:

3

I encountered a similar issue in a project where we needed to standardize employee codes. The SUBSTRING function combined with CHARINDEX can effectively solve this problem. Here’s a query that should work:

SELECT ID, NAME_CODE,
    CASE
        WHEN CHARINDEX('-', NAME_CODE) > 0
        THEN SUBSTRING(NAME_CODE, 1, CHARINDEX('-', NAME_CODE) - 1)
        ELSE NAME_CODE
    END AS cleaned_name
FROM EMPLOYEE

This approach handles both cases where a hyphen is present and where it’s not. The CASE statement ensures that names without hyphens remain unchanged. It’s a robust solution that worked well in our production environment.

4

hey there, i’ve dealt with this before. try using SUBSTRING and CHARINDEX like this:

SELECT ID, NAME_CODE,
SUBSTRING(NAME_CODE, 1, CASE WHEN CHARINDEX(‘-’, NAME_CODE) > 0 THEN CHARINDEX(‘-’, NAME_CODE) - 1 ELSE LEN(NAME_CODE) END) AS cleaned_name
FROM EMPLOYEE

this should work for names with or without hyphens. hope it helps!