0

I have a field which can have several occurrence of a particular delimiter- in my case it's '*'.The delimiter can appear a maximum of 10 times in the field to a minimum of 1 occurrence. it's not mandatory that all 10 occurrence will be there at a time in the field. Based on requirement I need to find the nth occurrence of the delimiter and return whatever data is available from delimiter to the start of the field after removing occurrence of the delimiter as output.

NOTE - I don't have write access to DB so cannot create User Defined Function to try out instring/substring combo.

Example:

Stack\*over\*flow\*com\*exchange\*queries\*need\*answer\*for\*thisquery\*

Requirement#1-find 2nd occurrence of * and return whatever is on LHS after removing the *

answer-.Stackover

Requirement#2-find 5th occurrence of * and return whatever is on LHS after removing the *

Answer -Stackoverflowcomexchange

And this goes on till the 10th occurrence is found.
I was able to get the first occurrence and lhs data but not able to figure out rest of the occurrence's.

DB used is SQL server 2014.

Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
Jane p
  • 3
  • 2
  • Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) – Ryan Wilson Mar 04 '19 at 20:07
  • 2
    But don't use the accepted answer of that duplicate. It is truly dreadful. Scroll down past the accepted answer to Aaron Bertrand's and use that one. – Sean Lange Mar 04 '19 at 20:31

1 Answers1

0

Posible quick solution:

declare @a varchar(100), @i int = 5, @j int = 0;

select @a = 'Stack\*over\*flow\*com\*exchange\*queries\*need\*answer\*for\*thisquery\*';

select @a = @a + replicate('\*', @i); -- Avoid delimiter not found

while @i > 0
    select @j = charindex('*', @a, @j + 1), @i -= 1;

select replace(left(@a, @j), '\*', '') as Result
Xabi
  • 465
  • 5
  • 8