Get everything before a certain character in SQL

Question

I got the following entries in my database:

E01234-1-1
E01234444-1-800000000

I want to trim the entry so I get:

E01234-1
E01234444-1

So basically, I want everything before the second '-' regardless of the length

How can I solve it? Im using MS SQL SERVER 2012

I am using this but it only bring data from before first hyphen, not second hyphen

DECLARE @TABLE TABLE (STRING VARCHAR(100))

INSERT INTO @TABLE (STRING)
      SELECT 'E01234-1-1' 
UNION ALL SELECT 'E01234-1-200' 
UNION ALL SELECT 'E01234-1-3000' 
UNION ALL SELECT 'E01234-1-40000' 
UNION ALL SELECT 'E01234-1-500000' 
UNION ALL SELECT 'E01234-1-6000000' 
UNION ALL SELECT 'E01234-1-70000000' 
UNION ALL SELECT 'E01234444-1-800000000' 

SELECT LEFT(STRING, CHARINDEX('-',STRING)-1) STRIPPED_STRING from @TABLE

RETURNS E01234 E01234 E01234 E01234 E01234 E01234 E01234 E01234444

Answer 1

If you need the second -:

SELECT 
    LEFT(STRING, CHARINDEX('-', @test, CHARINDEX('-', @test) + 1) -1) STRIPPED_STRING 
FROM @TABLE

Explanation: CHARINDEX will get you the index of the - - doing it twice (+ 1) specifies that the outter CHARINDEX should start at the spot after the first - in the string.

If you want to chop off everything after the last - instead (regardless of whether it's second or not):

SELECT 
    LEFT(STRING, LEN(STRING) - CHARINDEX('-', REVERSE(STRING))) STRIPPED_STRING
FROM @table

This time, you get the CHARINDEX of the last (reverse the string) -, and subtract that from the length of the whole string.