All Forums Database
sheridany 12 posts Joined 04/06
28 Apr 2006
extract text string from text field in column

I have a table with email addresses and I need to extract out the name before the @ in the address. Data looks likes this in the column.joenoname@noname.com? jillnoname@noname.combob.k.noname@noname.comThere are some null values in the column.The code I have tried is select trim(substr(C2T_EMAIL_ADDR_TXT,1,index(C2T_EMAIL_ADDR_ TXT, '@'))-1) from dvu_111.t_c2t_empor I have triedselect trim(substr(C2T_EMAIL_ADDR_TXT,1, position ('@' in C2T_EMAIL_ADDR_TXT))-1) as test from dvu_111.t_c2t_empand I get the following error message.bad character in format or data of t_c2t_emp.C2T_EMAIL_ADDR_TXTor I get another message like Code = 2663.2663: SUBSTR: string subscript out of bounds in T_C2T_EMP.C2T_EMAIL_ADDR_TXT. Anyone have any thoughts on how to do this?

DGiabbai 47 posts Joined 07/04
29 Apr 2006

In the first statement you misplaced "-1"Wrong : select trim(substr(C2T_EMAIL_ADDR_TXT,1,index(C2T_EMAIL_ADDR_TXT, '@'))-1) from dvu_111.t_c2t_empCorrect: select trim(substr(C2T_EMAIL_ADDR_TXT,1,index(C2T_EMAIL_ADDR_TXT, '@')-1)) from dvu_111.t_c2t_emp

You must sign in to leave a comment.