SQL Substring function usage

Question

This the column Description

Entity=10||WorkdayReferenceID=9000100332||[email protected]||FRP=||

I want to retrieve the emailid only in the above scenario the desired output would be

[email protected]

I tried using this:

select RTRIM(NVL(SUBSTR(TL.DESCRIPTION,(INSTR(TL.DESCRIPTION, '=',1,3)+1),
(LENGTH(TL.DESCRIPTION)-1)),'TL.DESCRIPTION'), '|') AS CCM 

But after that new value of FRP was added so it got wrong .

Answer 1

Assuming (and it is a big assumption) that there is only ever one @ in the data, the approach would be find the @, and then you want the text that is after the preceding = and before the next |. I do not know if the email address is always after HCMCostCenterMgr= so I won't assume that (but if that is the case the solution is easier).

It looks like you might be using Oracle (I see a NVL function), but I did this as SQL Server (it is more familiar off the top of my head). Here is a small script that will return just the email address you want - you can easily change these functions to Oracle if you need a version for Oracle.

DECLARE @data VARCHAR(200);
SELECT @data = 'Entity=10||WorkdayReferenceID=9000100332||[email protected]||FRP=||';

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING(@data, 1, CHARINDEX('|', @data, CHARINDEX('@', @data)) - 1)), 1, CHARINDEX('=', REVERSE(SUBSTRING(@data, 1, CHARINDEX('|', @data, CHARINDEX('@', @data)) - 1)))-1));

It is ugly, but SQL often gets a bit convoluted. I had to reverse the string to find things before the '@' and then reverse it at the end to get the value in the correct direction.