Extract string before certain character or without that character present

Question

I am using SQL Server 2016 and I am trying to extract the first set of numbers of a certain string. Here are some examples

12345
123456
12345-ks
12345-12
123456-ks

I want:

12345
123456
12345
12345
123456

I have tried SUBSTRING(@str, 0, charindex('-', str@, 0). But that excludes any strings without '-'

I have also tried using a case statement to include those strings. But, I can't group by a case statement. Any thoughts?

Answer 1

I've used a case statement to return the whole value when the column does not constain -.

create table strings ( s varchar(10));
insert into strings values
('12345'),
('123456'),
('12345-ks'),
('12345-12'),
('123456-ks');

select 
  case when charindex('-',s,0) = 0
  then  s
  else SUBSTRING(s,0,
          charindex('-', s, 0)
          ) 
  end as first_group
from strings;
GO

ci | first_group
-: | :----------
 0 | 12345      
 0 | 123456     
 6 | 12345      
 6 | 12345      
 7 | 123456     

db<>fiddle here