asked
extract substring between two delimters when more than one of those delimeters are present.
I have a database field in a table which contains a hierarchy of employee (numbers) by rank stacked side by side. The field name is [Primary Labour Account] example - "5005/37538/218469/3988/8438/5005.5821.06640/335733" As you likely guessed, the values are separated by a "/" delimiter. I have a need to only select the text between the second and third occurrence of the delimiter (represents supervisor number). So in my example i want the value "218469" I found this code which seems to be 96% of what i need but it only selects between the 1st and second delimiter. Seems whatever parameter in the statement I tween it never moves off this location. I want to force it to grab between 2 and 3. Can some please help? Keep in mind the length of each set of values varies. I'm new at a company trying to prove myself. This problem has stumped me for hours now. I don't want to waste any more of their time. I genuinely appreciate if anyone can offer a solution. SUbSTRING( /* > */ [Primary Labour Account], /* */ CHARINDEX('/',[Primary Labour Account],1) + 1, /* */ CHARINDEX('/', SUBSTRING([Primary Labour Account], CHARINDEX('/', [Primary Labour Account], +2) + 1, 99)) - 1 ) AS Result_string,
Here's an example that works out the positions of the 2nd and 3rd '/' using CHARINDEX, and uses those in a SUBSTRING function to strip out the characters you want. This will not work if there are less than 3 '/'s! declare @employeehierarchy table (somestring varchar(1000)); insert into @employeehierarchy (somestring) select '5005/37538/218469/3988/8438/5005.5821.06640/335733' insert into @employeehierarchy (somestring) select '5005/37539/218469/3988/8438/5005.5821.06640/335733' insert into @employeehierarchy (somestring) select '5005/37538/2184/3988/8438/5005.5821.06640/335733' insert into @employeehierarchy (somestring) select '5005/37538/2184676769/3988/8438/5005.5821.06640/335733' insert into @employeehierarchy (somestring) select '5005/37538/2/3988/8438/5005.5821.06640/335733' select substring( somestring, charindex('/',substring(somestring, charindex('/',somestring)+1, len(somestring))) + charindex('/',somestring)+1, (charindex('/', substring(somestring, charindex('/',substring(somestring, charindex('/',somestring)+1, len(somestring))) + charindex('/',somestring)+1, len(somestring))) + charindex('/',substring(somestring, charindex('/',somestring)+1, len(somestring))) + charindex('/',somestring)+1) - (charindex('/',substring(somestring, charindex('/',somestring)+1, len(somestring))) + charindex('/',somestring)+1) -1 ) from @employeehierarchy -------------------------- 218469 218469 2184 2184676769 2 (5 rows affected)
@never_2_late_2 I believe that the name is Kevan. If his answer provided a solution to your question, please tick it as accepted. You are the only one (outside of moderators) who can accept the answer because you are the one who asked the question. This way, other people who might have the same question in the future will see that there is already an accepted solution which works. Thank you.
No worries. It's an odd spelling, that's why I go by 'Kev' most of the time! :)
Kevin, Your a wizard! This is fantastic and works perfect. So grateful. regards /David
My Apologies (Kevan) on the spelling of your name. Thank you too OLEG for your feedback. My skills are of a 'basic' nature in SQL so I find that solution more code heavy than my skill set can absorb. :) I would like to 'tick' this as accepted but don't see how??? Thanks
@never_2_late_2 There is a "thumbs up" image to the top left of the answer and the number indicating the number of votes so far. Clicking the thumbs up icon should increase the number by 1 and change its colour in your view as well, to indicate that you have already voted. There should be another icon there which is visible to you but not to others (because you are the one who asked the question). The icon should indicate that you can click it to accept the solution.
Kevan's answer provides a perfectly working solution, but I would like to add my 2 cents exploiting other options. One of the other options (I ***do not*** recommend it, but it is there and may be mentioned) is to parse the values using XML. The idea is that if the delimiter is replaced with the end and start of an arbitrarily named tag and then the start tag is added to the left and end tag to the right, then the delimited string becomes looking like an XML fragment and can, therefore, be queried. Borrowing the table variable from @Kev Riley ♦♦ answer, the solution is: ;with records as ( select somestring, cast(' ' + replace(somestring, '/', ' ') + ' ' as xml) x from @employeehierarchy ), split as ( select records.somestring, item.value('.[1]', 'varchar(100)') [value], -- This is the fishy part: XML splits in ordered fashion, but so what! -- The final result might get reordered, so maxop 1 might be considered here row_number() over (partition by somestring order by (select null)) N from records cross apply x.nodes('r') r(item) ) select somestring, [value] SupervisorNumber from split where N = 3; Another option to consider is available if the SQL Server instance in question is 2016 or higher AND the compat level of the database in question is 130 or higher. If so then the [string_split][1] may be used to come up with the solution: ;with records as ( select somestring, [value], -- This is just as bad as the one in XML split, no guarantee of the order row_number() over (partition by somestring order by (select null)) N from @employeehierarchy cross apply string_split(somestring, '/') ) select somestring, [value] SupervisorNumber from records where N = 3; Both of the solutions rely on the splitting to be done in the ordered fashion. Unfortunately, I never heard of any guarantees about the **string\_split** values ordering. There are comments on the bottom of the linked docs article, including the post by @Magnus Ahlkvist who raises very valid concern about the possible lack of the order. Hope this helps. Oleg [1]:
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql
18 People are following this question.
