SQL Select everything after character

Question

I'd like to select everything AFTER a certain character (-) that is placed on the most right side.

Eg.

abcd-efgh-XXXX

And I'd like to select the XXXX part

Thanks!

before everything from the right? as in the last X chars? (where x is a static number). or everything from the last - on? need some more examples, or a better description of what exactly you are looking for . — Doon, Aug 16 '16 at 11:37
if length of `XXXX` this part is same for all then you can use right(column name,4) — Jaydip Jadhav, Aug 16 '16 at 11:40
Sorry, I've edited the question. Everything up to the first -, starting from the right. — coblenski, Aug 16 '16 at 11:41

score 92 · Accepted Answer · answered Aug 16 '16 at 11:41

92

You can use:

select right(col, charindex('-', reverse(col)) - 1)

answered Aug 16 '16 at 11:41

Gordon Linoff

1,242,037
58
646
786

10

Thank you for just answering the question, and not questioning or querying the intentions of the question asker. – shawty May 19 '21 at 11:10

score 5 · Answer 2 · answered Aug 16 '16 at 11:43

5

DECLARE @x varchar(100)
SET @x = 'abcd-efgh-XXXX'
SELECT RIGHT(@x, CHARINDEX('-', REVERSE(@x)) - 1)

answered Aug 16 '16 at 11:43

ADyson

57,178
14
51
63

TheGameiswar · Answer 3 · 2016-08-16T11:50:12.557

2

Using string split available from SQLServer 2016

;with cte
as
(
 select 
*,row_number() over (order by (select null)) as rownum
 from string_split('abcd-efgh-XXXX','-')
)
select top 1 * from cte 
order by rownum desc

edited Aug 16 '16 at 11:50

answered Aug 16 '16 at 11:41

TheGameiswar

27,855
8
56
94

This was a good concept. I'm on SQL 2014, so I don't have string_Split available, but I do have a user defined split function, that did the trick. Thanks for the nudge in the right direction! – KirstieBallance May 02 '18 at 19:00

KirstieBallance · Answer 4 · 2018-05-14T15:14:22.373

1

@thegameiswar had a clever solution, since I needed the results from a comma delimited list. I don't have SQL 2016, so I made it work with a user defined split function.

;with cte
as
(
 select 
 *,row_number() over (order by (select null)) as rownum
 from database..[fn_SplitDelimitedList](@CommaDelimitedList,',')
)
select * from cte 
order by rownum desc

edited May 14 '18 at 15:14

answered May 02 '18 at 19:02

KirstieBallance

1,238
12
26

score 0 · Answer 5 · edited Jul 16 '19 at 06:46

0

select substr('Prueba,Prueba2',instr('Prueba,Prueba2',',') + 1) from dual

edited Jul 16 '19 at 06:46

4b0

21,981
30
95
142

answered Jul 16 '19 at 06:41

user11790343

11

1

`instr` is not known in my sql server. – slartidan Jun 26 '20 at 09:54
Pretty sure that answer was for Oracle (based on the from dual) – Traderhut Games Apr 20 '21 at 17:22

score 0 · Answer 6 · edited Apr 19 '22 at 17:09

0

SQL Server Management Studio v15.0.18206.0 (18.4):

RIGHT([col], CHARINDEX('-', REVERSE([col]), -1))

edited Apr 19 '22 at 17:09

Ajay2707

5,690
6
40
58

answered Nov 20 '19 at 08:46

meraxes

1

1

Hey meraxes! Welcome to stack overflow. You might want enclose your sql query in a code block. – ali Nov 20 '19 at 08:52

score 0 · Answer 7 · answered Jul 16 '23 at 19:41

This is how to do the opposite of this question. i.e everything before the certain character that's placed on the right most side. If anyone found this question looking for the opposite like I did...

DECLARE @x varchar(100)
SET @x = 'abcd-efgh-XXXX'
SELECT LEFT(@x,LEN(@x) - CHARINDEX('-', REVERSE(@x)))

Then you would get abcd-efgh

Max Wulff · Answer 8 · 2023-02-27T19:50:20.167

-1

Worked for me in the case you are using postgres:

SELECT RIGHT(col, POSITION('-' IN REVERSE(col))-1)

Swap out POSITION for CHARINDEX

edited Feb 27 '23 at 19:50

answered Feb 27 '23 at 19:48

Max Wulff

1
2

I think OP mentioned sql server. – Urvah Shabbir May 23 '23 at 05:40

SQL Select everything after character

8 Answers8

Linked