How to get sub string till 2nd occurence of delimiter

Question

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

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.

Part of your problem is that you're trying to apply a SQL Server solution to a Redshift problem, and the two databases implement `CHARINDEX` differently, as indicated in the error message. The Redshift version does not include the third parameter, which the SQL Server code is trying to increment. — Eric Brandt, Feb 25 '20 at 14:15

score 2 · Accepted Answer · answered Feb 25 '20 at 14:16

2

You can use SPLIT_PART

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

answered Feb 25 '20 at 14:16

CarlosSR

score 0 · Answer 2 · answered Feb 25 '20 at 14:39

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;

in your case:-

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

2 Answers2