Search code examples
oracleplsqloracle-sqldeveloperplsqldeveloper

Remove string after second comma Oracle / PL SQL

I have this value '45465,6464,654' And I want to remove second comma and string after it. So basically I want ''45465,6464' . I found a solution but its for Mssql. How can I make this query for Oracle I couldnt do it even with substring. Can you help me?

This for MSSQL;

`declare @S varchar(20) = '45465@6464@654';

select left(@S, charindex('@', @S, charindex('@', @S)+1)-1);`

Solution

  • You can use something very similar:

    WITH s AS (SELECT '45465@6464@654' s FROM dual)
SELECT SUBSTR(s,1,INSTR(s,'@',1,2)-1) FROM s

    or you can use regular Expression:

    SELECT regexp_substr('45465@6464@654','([^@]*@)?[^@]*') from dual