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!

Noguchi

before what? What do You mean from the right? – Indecisive

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 . – Uneducated

if length of XXXX this part is same for all then you can use right(column name,4) – Clint

Sorry, I've edited the question. Everything up to the first -, starting from the right. – Noguchi

Do You mean AFTER the MOST right certain character? – Indecisive

Answer 1

You can use:

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

Loverly

Thank you for just answering the question, and not questioning or querying the intentions of the question asker. – Solitta

add the following constraint. where charindex('-', col) > 0 – Hailey

Answer 2

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

Intertexture

Answer 3

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

Beady

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! – Genuflect

Answer 4

@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

Genuflect

Answer 5

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

Leeannaleeanne

instr is not known in my sql server. – Maricruzmaridel

Pretty sure that answer was for Oracle (based on the from dual) – Arlyn

Answer 6

SQL Server Management Studio v15.0.18206.0 (18.4):

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

Pettitoes

Hey meraxes! Welcome to stack overflow. You might want enclose your sql query in a code block. – Tarp

Answer 7

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

Vassell

Answer 8

Worked for me in the case you are using postgres:

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

Swap out POSITION for CHARINDEX

Khaddar

I think OP mentioned sql server. – Jacynth