Get substring until a specific character starting from right to left SQL

Question

I have a string column where I need to find the substring until the first '-' is found.

Example column ELOT-IGS-2. I need to get "2" as output. These columns come from a table so I cannot declare the variable as a fixed string.

I tried LOCATE, SUBSTRING_INDEX but none are build in functions.

I also tried RIGHT(ID_BETSLIP,CHARINDEX('-',ID_BETSLIP)-1) but this does not work when I have 2 times "-"

Does anyone have an idea?

Check this [LINK](http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) or this [LINK](http://stackoverflow.com/questions/10443462/how-to-get-the-numeric-part-from-a-string-using-t-sql) — Abhi, Jun 09 '15 at 06:49
Can I declare a varibale that comes from a column in a table? — Yves Vanlathem, Jun 09 '15 at 06:55

score 4 · Answer 1 · answered Jun 09 '15 at 10:56

4

select RIGHT(<your Field>, CHARINDEX('-',REVERSE(<your Field>))-1)

answered Jun 09 '15 at 10:56

Ali pishkari

mohan111 · Answer 2 · 2015-06-09T06:55:41.447

2

DECLARE @t varchar(20) = 'ELOT-IGS-2'
select REVERSE(SUBSTRING(reverse(@t),0,CHARINDEX ('-',REVERSE(@t))))

edited Jun 09 '15 at 06:55

answered Jun 09 '15 at 06:52

mohan111

Almost man! I get for column PIK3-99701 as output "10799". Any way to fix this? – Yves Vanlathem Jun 09 '15 at 06:54
Igor posted something which worked but deleted his post.SUBSTRING(ID_BETSLIP, LEN(ID_BETSLIP) - CHARINDEX('-', REVERSE(ID_BETSLIP))+2, len(ID_BETSLIP)) as TEST – Yves Vanlathem Jun 09 '15 at 06:58

score 0 · Answer 3 · edited Feb 18 '22 at 18:18

Based on @mohan111's solution you can add REVERSE in order to get what you need
```
DECLARE @t varchar(20) = 'ELOT-IGS-2'
select REVERSE(SUBSTRING(reverse(@t),0,CHARINDEX ('-',REVERSE(@t))))
```
You should have done this yourself, once you got @mohan111's solution! For your own improvement you can not ask for everything :-( Once you got a solution that is almost what you need, it is time to try to improve it yourself. Please check this MSDN page, and every time that you need to parse a string, START HERE :-) Try to go over the functions and maybe you will find the solution.
The REVERSE query and most string parsing function are not good solution in SQL Server, and in most cases you are better to do this using SQLCLR function.

score 0 · Answer 4 · answered Jun 09 '15 at 07:00

0

Simplest would be

select SUBSTRING(reverse(ELOT-IGS-2),0,charindex('-',reverse(ELOT-IGS-2)))

answered Jun 09 '15 at 07:00

ThePravinDeshmukh

4 Answers4