I'm selecting an email address but I don't want to display the full email. Only the part before the '@'. How can I cut it. I know how to display only certain amount of characters or numbers. But how do I specify to display only till the '@' symbol.
Thank you.
Recent versions of Informix SQL have the CHARINDEX() function which can be used to isolate where the '@' symbol appears:
SELECT LEFT(email_addr, CHARINDEX('@', email_addr)-1)
CHARINDEX() will return 0 if not found, otherwise the ordinal position of the located string. My testing found that LEFT() doesn't complain about being passed 0 or -1, so it's safe to execute this as is, you don't have to verify that you get something back from CHARINDEX() first.
CREATE TEMP TABLE ex1
(
email_addr VARCHAR(60)
) WITH NO LOG;
INSERT INTO ex1 VALUES ('ret@example.com.au');
INSERT INTO ex1 VALUES ('emdee@gmail.com');
INSERT INTO ex1 VALUES ('unknown');
INSERT INTO ex1 VALUES (NULL);
INSERT INTO ex1 VALUES ('@bademail');
SELECT LEFT(email_addr, CHARINDEX('@', email_addr)-1) FROM ex1
... produces:
(expression)
ret
emdee
5 row(s) retrieved.
If you have an older version of Informix that doesn't support CHARINDEX(), you'll be forced to iterate through the string character by character, until you find the '@' symbol.