How to get sub string till 2nd occurence of delimiter

0
votes

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.

2
sqlamazon-redshift
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

2 Answers

1
votes

You can use SPLIT_PART

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

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;