SQL SERVER select string from right after a certain character

Question

I have a bit of problem regarding sql select statement. I have a column value that look like this

    2>4>5 or
    28>30>52 or
    300>410>500 or
    2>4>5>8

My question is, how can i get the value from RIGHT after the > character, so the select statement from the value above will return

Thanks in advance

Your third row doesn't contain 400, just 410. Do you want the second number or the second to last (as the fourth example may show)? Please update the question to reflect your answer, and I'll be glad to help. — Steve Lovell, Apr 16 '17 at 09:35
Also your column contains multiple `>` characters. after which `>` character do you need get value ? — Oto Shavadze, Apr 16 '17 at 09:37
Sorry,it should return 410. I just want to get the second value from right of the string after the > character — user3127287, Apr 16 '17 at 09:39

Oto Shavadze · Accepted Answer · 2017-04-16T10:42:47.647

1

If you need second value from right, then try:

SELECT SUBSTRING_INDEX( SUBSTRING_INDEX(your_column, '>', -2), '>', 1);

EDIT

One solution for sql server:

DECLARE @str varchar(max);
set @str = '2>4>5>8';

SELECT reverse( substring(
    substring( reverse(@str),  charindex( '>', reverse(@str) )+1, len(@str)  ),  0, 
    charindex( '>', substring( reverse(@str),  charindex( '>', reverse(@str) )+1, len(@str)  ) ) 
    ) );

edited Apr 16 '17 at 10:42

answered Apr 16 '17 at 09:48

Oto Shavadze

40,603
55
152
236

Im using a sql server 2014, when i execute the query it returns SUBSTRING_INDEX is not a recognized built-in function name. – user3127287 Apr 16 '17 at 09:58
Then why you tagged your question as mysql ? – Oto Shavadze Apr 16 '17 at 10:00

score 1 · Answer 2 · edited May 23 '17 at 11:46

1

This is similar to extracting the n-th element from a delimited string. The only difference is that in this case we want the n-th-to-last element. The change can be achieved with a double use of reverse. Assuming the table is MyTable and the field is MyColumn, here's one way:

SELECT
    Reverse(
        CAST('<x>' + REPLACE(Reverse(MyColumn),'>','</x><x>') + '</x>' AS XML).value('/x[2]', --x[2] because it's the second element in the reversed string
        'varchar(5)' --Use something long enough to catch any number which might occur here
        ))
FROM
    MyTable

With credit to @Shnugo for his efforts here: Using T-SQL, return nth delimited element from a string

You can't cast as an int where I've put varchar(5)since at that stage the strings are still reversed. If you need to convert to an integer, do that by wrapping a convert/cast on the outside.

edited May 23 '17 at 11:46

Community

1
1

answered Apr 16 '17 at 10:22

Steve Lovell

2,564
2
13
16

This is exactly what i need, thank you so much Steve.You saved me a lot of time.. – user3127287 Apr 16 '17 at 10:33
upvote, huh, SQL server have so poor functionality for working with strings – Oto Shavadze Apr 16 '17 at 10:46
Yep @OtoShavadze, some things are much easier in MySQL. Not everything though. There are no Window Functions for example. I think it's because MySQL is so tied to web-technologies that it tends to have different drivers for the functionality. – Steve Lovell Apr 16 '17 at 10:51
`some things are much easier in MySQL`, also if comparing to another DB's, for example regex support is missing here, sometimes this is serious drawback, when working with strings. – Oto Shavadze Apr 16 '17 at 10:57

score 0 · Answer 3 · 2017-04-17T09:06:43.923

;WITH cte1(Value)
AS
(
SELECT  '2>4>5'      Union all
SELECT  '28>30>52'     Union all
SELECT  '300>410>500'  Union all
SELECT  '2>4>5>8' 
)
SELECT 
SUBSTRING(
(
REVERSE(SUBSTRING(((REVERSE((SUBSTRING(Value, RIGHT(CHARINDEX('>', Value), Len(Value)) + 1, Len(Value)))))),
CHARINDEX('>',((REVERSE((SUBSTRING(Value, RIGHT(CHARINDEX('>', Value), Len(Value)) + 1, Len(Value)))))))+1,LEN(Value)))
),CHARINDEX('>',(
REVERSE(SUBSTRING(((REVERSE((SUBSTRING(Value, RIGHT(CHARINDEX('>', Value), Len(Value)) + 1, Len(Value)))))),
CHARINDEX('>',((REVERSE((SUBSTRING(Value, RIGHT(CHARINDEX('>', Value), Len(Value)) + 1, Len(Value)))))))+1,LEN(Value)))
))+1,LEN(Value))

 AS ExpectedValue
FROM cte1

SQL SERVER select string from right after a certain character

3 Answers3