Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to grab characters before and after a string Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 2:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
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
Post #1345161
Posted Wednesday, August 15, 2012 2:40 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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 '%/%'




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1345168
Posted Wednesday, August 15, 2012 2:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
Lol! Thanks Phil, its works perfectly
Post #1345169
Posted Wednesday, August 15, 2012 2:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 3,861, Visits: 5,003
Consider using CHARINDEX()
e.g.
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



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1345176
Posted Thursday, August 16, 2012 1:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
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.
Post #1345695
Posted Friday, August 17, 2012 12:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:48 AM
Points: 1,118, Visits: 1,582
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



Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1346351
Posted Friday, August 17, 2012 2:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:22 PM
Points: 411, Visits: 1,399
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 '%/%'





Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1346385
Posted Friday, August 17, 2012 2:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 7,127, Visits: 13,501
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)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1346390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse