SQL Limit digits limit dash and characters to the right, but if NO dash still show the field data

Question

I'm using this in a SQL query in SQL Server Management Studio, so that if the column dbo.Material_Trans.Lot has a dash the dash and all the string to the right is removed.

SUBSTRING(dbo.Material_Trans.Lot, 0, CHARINDEX('-', dbo.Material_Trans.Lot))

However, if the dbo.Material_Trans.Lot column does not contain a '-', then it returns nothing. I would like an IIF statement that if the dbo.Material_Trans.Lot does not contain a '-' return the dbo.Material_Trans.Lot column, else do the

SUBSTRING(dbo.Material_Trans.Lot, 0, CHARINDEX('-', dbo.Material_Trans.Lot))

Thank you!

I have tried the IIF and it fails within SQL as invalid statement.

Answer 1

You can create a unique constraint over a generated column. For example:

create table t (a varchar(20));

insert into t (a) values ('Hello-World'), ('Theremin'), ('-123');

alter table t add clean_data as case when charindex('-', a) = 0 
                                then a
                                else substring(a, 0, charindex('-', a) ) 
                                end;

alter table t add constraint uq_clean_data unique (clean_data);

insert into t (a) values ('Happy'); -- Succeeds

insert into t (a) values ('Hello-Tom'); -- Fails as expected

Result:

 a            clean_data 
 ------------ ---------- 
 Hello-World  Hello      
 Theremin     Theremin   
 -123                    
 Happy        Happy      

See running example at db<>fiddle.

Answer 2

You should generally prefer CASE expressions over IIF() as IIF() is not part of the ansi standard. But COALESCE() + NULLIF() can also work here and may be more efficient:

SUBSTRING(dbo.Material_Trans.Lot, 0,    
   COALESCE(NULLIF(CHARINDEX('-', dbo.Material_Trans.Lot),0),LEN(dbo.Material_Trans.Lot)) 
)

The CASE expression would look like this:

CASE WHEN CHARINDEX('-', dbo.Material_Trans.Lot) > 0 THEN
      SUBSTRING(dbo.Material_Trans.Lot, 0, CHARINDEX('-', dbo.Material_Trans.Lot))  
    ELSE dbo.Material_Trans.Lot END