CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user3059409
user3059409

Reputation: 25

How can I extract values from a string in SQL

My column has different length values and I want to extract all the values that are before the 2nd dash. Here are some of the values in the column

1AA-00001-20170101
AAAA-10010-20161201
1BBB2-22222-20151105

How do I get the following values in SQL

1AA-00001
AAAA-10010
1BBB2-22222

Upvotes: 0

Views: 79

Answers (3)

Alan Burstein
Alan Burstein

Reputation: 7918

Another way

-- sample data
declare @table table (somestring varchar(100));
insert @table 
values ('1AA-00001-20170101'), ('AAAA-10010-20161201'),('1BBB2-22222-20151105');

-- solution
select somestring, newstring = 
         parsename(replace(somestring,'-','.'),3)+'-'+
         parsename(replace(somestring,'-','.'),2)
from @table;

Results

somestring            newstring
--------------------- ------------
1AA-00001-20170101    1AA-00001
AAAA-10010-20161201   AAAA-10010
1BBB2-22222-20151105  1BBB2-22222

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The structure of your strings seems pretty canonical. It seems you can do:

select left(col, len(col) - 9)

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use:

SELECT LEFT(col, CHARINDEX( '-', col, CHARINDEX('-', col)+1)-1)
FROM table;

Rextester Demo

Upvotes: 2

Related Questions