Pull values to the Left of a dash

Question

I am trying to pull from a field called "Location" that includes a number of varying lengths after a dash. I only want to pull the value of the field before the dash. My expression below worked with the LTRIM to pull values after the dash but is not working for values before. What is wrong with the below statement?

COALESCE(RTRIM(SUBSTRING(Location,CHARINDEX('-',Location,1)+1

LEN(Location)-CHARINDEX('-',Location,1))),Location)

Answer 1

SELECT COALESCE(SUBSTRING(Location,0,  
CHARINDEX('-',Location)),Location)  
FROM #t  
  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @Evan Stroum ，

Please refer to:

CREATE TABLE #t(Location char(55))  
INSERT INTO #t VALUES('Newport Plant - 10601 - L48 Production'),  
                     ('Sandpoint - 13501 - AK Shore QA')  
  
SELECT LEFT(Location,CHARINDEX('-',Location)-1)  
FROM #t  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I should have clarified that I have thousands of fields I need the values for to the left of the dash.

Answer 4

I am also relegated to what functions Dayforce uses for SQL.

Answer 5

It's a custom field editor for SQL within Dayforce.