Using right() and len() together is a bit dangerous. Consider what happens if an end user enters an additional space after one entry:

declare @ChemInventory table (

[Room / Shelf] varchar(200) not null

);

insert @ChemInventory

values ('Flammables/solvents');

insert @ChemInventory

values ('TC fridge/freezer');

insert @ChemInventory

values ('U2-16/Freezer');

insert @ChemInventory

values ('U2-16/Fridge');

insert @ChemInventory

values ('U2-16/Poisons cabinet');

insert @ChemInventory

values ('U2-16/12345 ');

select [Room / Shelf]

,left([Room / Shelf], charindex('/', [Room / Shelf]) - 1)

,right([Room / Shelf], len([Room / Shelf]) - charindex('/', [Room / Shelf]))

From @ChemInventory

Where [Room / Shelf] LIKE '%/%'

The output of the last line is now '2345 ', instead of the expected '12345 '. I think that trailing space won't be missed in most applications, so for many applications you're safe with the use of len(). If not use datalength() instead of len() -and watch out if the type is nvarchar, because then you need to divide the length by 2-. But you should not use right() and len() together.

And another potential pitfall, that you luckily won't see in this example because of the "where [Room / Shelf] like '%/%'" is the fact that substring() (but right() does exactly the same thing), won't accept a negative (or null) length parameter. So if any of the rows doesn't have the "/" in it, the result will be an error message "Msg 536, Level 16, State 5, Line 22 Invalid length parameter passed to the SUBSTRING function.". If you're splitting up to 2 items it is easy, because you can instead of the calculated length of the 2nd part just specify the maximum length of the entire column: substring() will copy just the amount of available characters any way. If you need to split more "fields", you would need "case when then end"-constructs to avoid this nasty error. This is why it is often easier to first split the string into rows at the separator(s) using for example a string splitter function and then pivot the resulting rows back into columns using a cross tab. See my foot notes for links to the articles explaining these techniques.

So here is an example that sets off both errors in the original code and then fixes them:

declare @ChemInventory table (

[Room / Shelf] varchar(200) not null

);

insert @ChemInventory

values ('Flammables/solvents');

insert @ChemInventory

values ('TC fridge/freezer');

insert @ChemInventory

values ('U2-16/Freezer');

insert @ChemInventory

values ('U2-16/Fridge');

insert @ChemInventory

values ('U2-16/Poisons cabinet');

insert @ChemInventory

values ('U2-16/12345 ');

insert @ChemInventory

values ('2');

select [Room / Shelf]

,case when charindex('/', [Room / Shelf]) > 0 then left([Room / Shelf], charindex('/', [Room / Shelf]) - 1) else [Room / Shelf] end

-- ,right([Room / Shelf], len([Room / Shelf]) - charindex('/', [Room / Shelf]))

-- Don't need to calculate the exact length, because I only need the remainder of the string.

,case when charindex('/', [Room / Shelf]) > 0 then substring([Room / Shelf], charindex('/', [Room / Shelf]) + 1, 200) end

From @ChemInventory

--Where [Room / Shelf] LIKE '%/%'