CODEWITHSUNDEEP
sqlsql-server-2016
coblenski
coblenski

Reputation: 1189

SQL Select everything after character

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!

Upvotes: 40

Views: 161145

Answers (8)

ocean800
ocean800

Reputation: 3737

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

Upvotes: -1

Max Wulff
Max Wulff

Reputation: 1

Worked for me in the case you are using postgres:

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

Swap out POSITION for CHARINDEX

Upvotes: -1

meraxes
meraxes

Reputation: 1

SQL Server Management Studio v15.0.18206.0 (18.4):

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

Upvotes: 0

user11790343
user11790343

Reputation: 11

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

Upvotes: 0

KirstieBallance
KirstieBallance

Reputation: 1358

@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

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28938

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

Upvotes: 2

ADyson
ADyson

Reputation: 62098

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

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You can use:

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

Upvotes: 94

Related Questions