CODEWITHSUNDEEP
sqlamazon-redshift
MAC
MAC

Reputation: 1515

How to get sub string till 2nd occurence of delimiter

I am trying to get sub string till 2nd occurence of delimiter, but some how its not working. I followed several stackoverflow links:

SQL Server - find nth occurrence in a string

Here is the column in my database:

this+is+my
never+ending+movie
hello

Required output

this+is
never+ending
hello

I tried: 

charindex('+', col1, (charindex('+', col1, 1))+1)

Error: SQL Error [500310] [42883]: Amazon Invalid operation: function charindex("unknown", character varying, integer) does not exist;

I am using Amzaon Redshift database and Dbeaver to connect.

Upvotes: 0

Views: 2655

Answers (2)

this.girish
this.girish

Reputation: 1306

you can use left with Charindex, try following

declare @S varchar(20) = 'this+problem+is+fixedby+Girish';
select left(@S, charindex('+', @S, charindex('+', @S)+1)-1) as Result;

here is dbfiddle

in your case:-

select left(columnname, charindex('+', columnname, charindex('+', @S)+1)-1) from your table as Result;

Upvotes: 0

CarlosSR
CarlosSR

Reputation: 1195

You can use SPLIT_PART

select split_part(col1,'+',1) || '+' || split_part(col1,'+',2) as string_value
from table_name;

Upvotes: 2

Related Questions