how to strip email address off a field - Tibor Karaszi

Here's one possible way. It all boils down to quality of data in the end...

DECLARE @a varchar(255)

SET @a = 'John Doe [jdoe@home.com]'

SELECT SUBSTRING(@a, CHARINDEX('[', @a) + 1, LEN(@a) - CHARINDEX('[', @a) - 1)


And perhaps you should re-consider storing multiple information in the same column... :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

how to strip email address off a field - Plamen Ratchev

You can use SUBSTRING and CHARINDEX to extract the email portion. Since you
will be joining on that expression it is a good idea to make it a computed
column and add an index. Here is how it may look. Note this does not do any
special handling if the column does not have a valid email (you can use CASE
to check if CHARINDEX returns correct position). Also, on SQL Server 2005
using CLR and regular expressions can make this very easy.

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
name_email NVARCHAR(255));

INSERT INTO Foo VALUES(1, 'John Doe [jdoe@home.com]');

GO

ALTER TABLE Foo
ADD email AS SUBSTRING(name_email,
CHARINDEX('[', name_email) + 1,
CHARINDEX(']', name_email) -
CHARINDEX('[', name_email) - 1);

GO

CREATE INDEX ix_email
ON Foo (email);

GO

SELECT keycol, name_email, email
FROM Foo;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

how to strip email address off a field - jtammy

hi!

I have the following nvarchar(255) field:

John Doe [jdoe@home.com]


I need to be able to see only jdoe@home.com by using a select query.

How can I achieve this easily?

Eventually this will be part of a join of tables.

Thanks in advanced.

T

how to strip email address off a field - jtammy

Thank you so much, Tibor and Plamen...both ur solutions work great!!!!

T