asked
Substring Question
I have a series of strings that are similar. All of them are in an ID column. Most of them are formatted this way MDD04_V_V17023. But some of them have more added on to the end. For example, MDD04_V_V17023_BEDP. I just want to grab the V17023 which is the same for all of the strings. How would I do that? This is a watered down version of what I currently have. The SUBSTRING function I thought was perfect for this but it keeps grabbing all the characters after the 9th character. It will grab V17023_BEDP instead of just V17023. This seems to be a little more involved and I am new. Please help if you can. Thanks! SELECT AccountNo,SUBSTRING(ID , 9,14) FROM table WHERE ID LIKE 'MDD04_V%'
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer. And you can pick your own answer.
thanks @GrantFritchey. I wish I could choose all the answers as correct. The first answer was correct for what I was asking originally. The next answer was correct as well. They were all correct and lead me to solving my own problem. So for anyone reading this, depending on your current situation, any of these answers could be a solution. Thanks!
That's great to hear. Vote for 'em all, but there can only be a single winner. Sorry.
So IDK if this is taboo or not but I figured out what I was trying to do and this is it. Here is the answer to what is the best way(that i know of) to split up a string of characters from a particular column with multiple values, and grab a particular element. First build your tally table/ delimiter function... - First build your tally table/ delimiter function
http://www.sqlservercentral.com/articles/Tally+Table/72993/ --Read this article over and over if you have to, it helped me a lot to understand some concepts I had no clue about before, because I'm new. - Then use "Cross Apply" to select the elements of a particular string that you want. EX. SELECT a.Column, b.Item as 'CoolColumn' FROM TABLE a CROSS APPLY (SELECT Item FROM DelimitedSplit8k(a.column, 'delimitergoeshere')WHERE ItemNumber = 'Put the element you are trying to grab here'--This should be an int) as b Hope this Helps! Everyone here is awesome!
the [`SUBSTRING`][1] function's third parameter is the length of the string to be returned, not the last character of the original string. Try SELECT AccountNo,SUBSTRING(ID , 9, 6) FROM table WHERE ID LIKE 'MDD04_V%' [1]:
https://msdn.microsoft.com/en-us/library/ms187748.aspx
Depends on how much control you have over the incoming data. you could get away with a combination of normal T-SQL String functions, such as SUBSTRING and CHARINDEX (or PATINDEX), but you may find that a more generic approach may be required. Try reading about the String Splitter function at
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Thanks for that. Sorry if it was a dumb question. This may be a little more interesting... So what if the V17023 that I want to grab isn't 6 characters? What if some of them were 5 characters? What if I just need to capture the value between the second underscore and the last underscore, if there is one?
@ThomasRushton is right that you can use string functions, and even more right that a string splitter would be a better way foward. Here however (for fun) is the charindex and substring way of doing it declare @YourTable table (ID varchar(max)) insert into @YourTable select 'MDD04_V_V17023' insert into @YourTable select 'MDD04_V_V17023_BEDP' insert into @YourTable select 'MD04_V_V17023_BEDP' select --position of 2nd underscore charindex('_', ID, charindex('_', ID)+1), --position of possibly 3rd underscore charindex('_',ID,charindex('_', ID, charindex('_', ID)+1)+1), --length of value we are interested in is difference between posn of 2nd and posn of 3rd --use these values in a case expression case when charindex('_',ID,charindex('_', ID, charindex('_', ID)+1)+1) = 0 --there is no 3rd underscore then --substring from after 2nd to end of string substring(ID, charindex('_', ID, charindex('_', ID)+1) +1, len(ID)) else --susbtring from 2nd to 3rd substring(ID, charindex('_', ID, charindex('_', ID)+1)+1, charindex('_',ID,charindex('_', ID, charindex('_', ID)+1)+1) - charindex('_', ID, charindex('_', ID)+1)-1) end from @YourTable gives -------------------- -------------------- ------------- 8 0 V17023 8 15 V17023 7 14 V17023
so...(for fun lol) is there a way to turn this into a function that just returns the "V17023" or should I ask tat question separately? I have already tried to build it and I get the error ..."Only one expression can be specified in the select list when the sub query is not introduced with EXISTS."... I have my return variable set for the above query with parameters for ID and the delimiter.
create function dbo.YourFunction_ITVF (@inputstring varchar(max)) returns table return select case when charindex('_',@inputstring,charindex('_', @inputstring, charindex('_', @inputstring)+1)+1) = 0 --there is no 3rd underscore then --substring from after 2nd to end of string substring(@inputstring, charindex('_', @inputstring, charindex('_', @inputstring)+1) +1, len(@inputstring)) else --susbtring from 2nd to 3rd substring(@inputstring, charindex('_', @inputstring, charindex('_', @inputstring)+1)+1, charindex('_',@inputstring,charindex('_', @inputstring, charindex('_', @inputstring)+1)+1) - charindex('_', @inputstring, charindex('_', @inputstring)+1)-1) end as CleanString go declare @YourTable table (ID varchar(max)) insert into @YourTable select 'MDD04_V_V17023' insert into @YourTable select 'MDD04_V_V17023_BEDP' insert into @YourTable select 'MD04_V_V17023_BEDP' select ID, CleanString from @YourTable YT cross apply dbo.YourFunction_ITVF (
YT.ID)
17 People are following this question.
