extract text string from text field in column

Database
Enthusiast

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.com
bob.k.noname@noname.com

There 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_emp

or I have tried
select trim(substr(C2T_EMAIL_ADDR_TXT,1, position ('@' in C2T_EMAIL_ADDR_TXT))-1) as test from
dvu_111.t_c2t_emp
and I get the following error message.

bad character in format or data of t_c2t_emp.C2T_EMAIL_ADDR_TXT
or 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?

1 REPLY
Enthusiast

Re: extract text string from text field in column

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_emp
Correct: select trim(substr(C2T_EMAIL_ADDR_TXT,1,index(C2T_EMAIL_ADDR_TXT, '@')-1)) from dvu_111.t_c2t_emp