SQL - Getting Index of delimiter found in the variable and substring based on the index

Question

I need to get the last occurrence of symbol found in multiple same symbol.

SELECT Charindex('|', 'abc||xyz')

This will result 4, but i need to get the index of the last '|' which is index of 5. Then substring the string based on that index.

EDIT: I'm sorry I am wrong with my question earlier. I forgot to mention that the string may contains more number of characters and symbols.

@forpas answer is correct if the string 'abc||xyz'.

But this will become wrong if the string is 'abc||xyz|||qwe'

EDIT 2: Basically I want to substring the string including the delimiter while looping the string.

Declare 
    @txt as nvarcar(max), 
    @newTxt as nvarchar(50)
SET @txt = 'dog|cat|||fish|horse||||frog'

--while looping

--1st loop:
@newTxt = 'dog|'
@txt = 'cat|||fish|horse||||frog'

--2nd loop:
@newTxt = 'cat|||'
@txt = 'fish|horse||||frog'

--3rd loop:
@newTxt = 'fish|'
@txt = 'horse||||frog'
...

UPDATE The original question is about finding index in the given string. But due to several updates because of the lacking information, the other answer conflicts with the new question. After the 2nd edition, the whole process what I was doing suddenly completed by the accepted answer. So the title of the question and some context are updated for this thread to be useful.

So when the string is `abc||xyz|||qwe` what would be the index you want? — forpas, Jun 17 '19 at 07:49
@forpas hi sir, sorry for the lack of info on my first question. I still need the index of 5 cause later I will remove the characters before and including the index found. — jymskrl, Jun 17 '19 at 07:51
@forpas then after the new string will become 'xyz|||qwe' after substring of the first few characters. So the next index will be 6. — jymskrl, Jun 17 '19 at 07:52
Your requirement is incomprehensible. What is the logic about it? You need to clarify. — forpas, Jun 17 '19 at 07:56
I'm looping to a string with multiple '|' delimiter. Then i need to get (substring) the words between those delimiter but sometimes it has multiple symbol which causes my charindex failing because the index is not properly found. — jymskrl, Jun 17 '19 at 08:00
What is so special about that delimiter in the 5th position? This is what you need to explain. Why that? — forpas, Jun 17 '19 at 08:02
Because after I get the index of 5 I will use it to substring the whole string to 'abc||' then the remaining 'xyz|||qwe' will be the next value of the variable in my query. Then the next substring will be 'xyz|||' and so on.... — jymskrl, Jun 17 '19 at 08:06

forpas · Answer 1 · 2019-06-17T08:12:14.187

3

This answers the original question:

I need to get the last occurrence of symbol found in multiple same symbol.

Use charindex() with the reversed string and subtract it from the length of the string:

select len('abc||xyz') - charindex('|', reverse('abc||xyz')) + 1

For the case that '|' does not exist in the string, if you want 0 returned, use this:

select (len('abc||xyz') - charindex('|', reverse('abc||xyz')) + 1) % (len('abc||xyz') + 1)

See the demo.

edited Jun 17 '19 at 08:12

answered Jun 17 '19 at 07:38

forpas

160,666
10
38
76

Duplicate of https://stackoverflow.com/questions/39002025/is-there-a-lastindexof-in-sql-server/39002164 – Programnik Jun 17 '19 at 07:55

score 1 · Accepted Answer · answered Jun 17 '19 at 08:41

1

You can use this SplitString function to get the list of values with the index they where found and adapt it to your purpose....

CREATE FUNCTION [dbo].[fn_SplitString2] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(id int not null primary key identity, splitdata NVARCHAR(MAX), startindex int) 

BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata, startindex)  
        VALUES(SUBSTRING(@string, @start, @end - @start), @start) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END



DECLARE @st VARCHAR(100) = 'dog|cat|||fish|horse||||frog';

SELECT *
FROM [dbo].[fn_SplitString2](@st, '|') A
WHERE splitdata <> ''

OUTPUT:

id splitdata startindex

1 dog 1
2 cat 5
5 fish 11
6 horse 16
10 frog 25

answered Jun 17 '19 at 08:41

Daniel Brughera

1,641
1
7
14

I'm already in the process of looping and getting and setting of start and end index but your whole query just completed what I was doing. – jymskrl Jun 17 '19 at 09:00
However, neither the title nor the question describe it, even after your second edition, I had to infer it from your sample, I'm glad to help you but this post won't be too helpful for other people in the future, once you got a solution, please edit your post to add a better description of the problem – Daniel Brughera Jun 17 '19 at 09:13
Yeah, sorry I'm new to Q&A here. Should I need to change the title of the question? – jymskrl Jun 17 '19 at 09:15
In my opinion, both. With that the title I don't need to read the question to be sure that @forpas's answer is the one – Daniel Brughera Jun 17 '19 at 09:17
I see. I'll change the title and some description in my question for this to be useful. Thanks – jymskrl Jun 17 '19 at 09:20

score 0 · Answer 3 · edited Jun 18 '19 at 08:58

0

This will also get (4)

SELECT CHARINDEX('|', 'abc||xyz', 1) AS MatchPosition;

edited Jun 18 '19 at 08:58

piet.t

11,718
21
43
52

answered Jun 17 '19 at 07:32

prince nhiel cada

74
12

I need the index not the substring result – jymskrl Jun 17 '19 at 07:34

score 0 · Answer 4 · edited Jun 17 '19 at 07:59

0

Try this one

SELECT INSTR('abc||xyz','|',1,REGEXP_COUNT('abc||xyz','|'))
"PipeSearch" FROM DUAL;

edited Jun 17 '19 at 07:59

forpas

160,666
10
38
76

answered Jun 17 '19 at 07:58

Mansi Raval

371
6
14

Sorry @mansi, can you provide me without using regex please? because I'm using sql server 2008 r2. – jymskrl Jun 17 '19 at 08:03

SQL - Getting Index of delimiter found in the variable and substring based on the index

4 Answers4