August 15, 2012 at 2:22 am
Hi,
I need to take the characters from the left before the '/' string and insert into a column and the characters on the right after the '/' string into another column.
This is how my sample data looks, its not all the same in the table :
/*--[Room / Shelf]
Flammables/solvents
TC fridge/freezer
U2-16/Freezer
U2-16/Fridge
U2-16/Poisons cabinet
*/
Select LEFT([Room / Shelf], 5)
From ChemInventory
Where [Room / Shelf] LIKE '%/%'
I don't want to count the characters as I have different records in my table, how can I achieve this without using the syntax above?
Thanks
Teee
August 15, 2012 at 2:40 am
Teee (8/15/2012)
Hi,I need to take the characters from the left before the '/' string and insert into a column and the characters on the right after the '/' string into another column.
This is how my sample data looks, its not all the same in the table :
/*--[Room / Shelf]
Flammables/solvents
TC fridge/freezer
U2-16/Freezer
U2-16/Fridge
U2-16/Poisons cabinet
*/
Select LEFT([Room / Shelf], 5)
From ChemInventory
Where [Room / Shelf] LIKE '%/%'
I don't want to count the characters as I have different records in my table, how can I achieve this without using the syntax above?
Thanks
Teee
Leaving aside the fact that a column called 'Room / Shelf' is just plain nasty :-), you need something like this:
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 absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 15, 2012 at 2:43 am
Lol! Thanks Phil, its works perfectly 🙂
August 15, 2012 at 2:57 am
This was removed by the editor as SPAM
August 16, 2012 at 1:05 am
SELECT LEFT(SearchCol, LEN(SearchCol)-CHARINDEX('/',SearchCol)-1) AS LeftMost, RIGHT(SearchCol, LEN(SearchCol)-CHARINDEX('/',SearchCol)+1) AS RightMost
FROM MyTable WHERE CHARINDEX('/',SearchCol) > 0
--------------------------------------------------------------------
Thanks this works as well.
August 17, 2012 at 12:44 am
This is on the same lines but a little different in logic:
Select LEFT([Flammables/solvents], (CHARINDEX('/', [Flammables/solvents]) - 1) ) As Flammables,
RIGHT([Flammables/solvents], (CHARINDEX('/', Reverse([Flammables/solvents])) - 1) ) As Solvents
From Ex
August 17, 2012 at 2:08 am
Phil Parkin (8/15/2012)
....
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 '%/%'
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 '%/%'
August 17, 2012 at 2:26 am
Returning NULL if the delimiter isn't found simplifies the code considerably:
SELECT
[Room / Shelf],
[Room] = LEFT([Room / Shelf], x.pos-1),
[Shelf] = SUBSTRING([Room / Shelf], x.pos+1,8000)
FROM @ChemInventory
CROSS APPLY (
SELECT NULLIF(CHARINDEX('/', [Room / Shelf]),0)
) x (pos)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply