CODEWITHSUNDEEP
sqlsql-servert-sql
thirty
thirty

Reputation: 177

Get everything before a certain character in SQL

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

Upvotes: 9

Views: 78838

Answers (3)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this:

DECLARE @STR NVARCHAR(MAX) = 'E01234444-1-800000000';
SELECT LEFT(@STR, CHARINDEX('-', @STR, CHARINDEX('-', @STR)) - 1)

Upvotes: 8

randomizer
randomizer

Reputation: 1649

If you are using MySQL use something like this:

SELECT SUBSTRING_INDEX(fieldname, '-', 2) FROM tablename

Upvotes: 1

Dan Field
Dan Field

Reputation: 21641

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.

Upvotes: 10

Related Questions