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

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.

CodePudding user response：

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.

CodePudding user response：

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