SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to grab characters before and after a string


How to grab characters before and after a string

Author
Message
Teee-SQL
Teee-SQL
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 229
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
Phil Parkin
Phil Parkin
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48490 Visits: 21121
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Teee-SQL
Teee-SQL
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 229
Lol! Thanks Phil, its works perfectly :-)
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16357 Visits: 7413
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”
Teee-SQL
Teee-SQL
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 229
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.
vinu512
vinu512
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3451 Visits: 1626
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 ;-)
R.P.Rozema
R.P.Rozema
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2682 Visits: 1716
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?
ChrisM@Work
ChrisM@Work
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39118 Visits: 19990
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search