CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user614978
user614978

Reputation: 317

how to get email-id from string in sql server?

DECLARE @str as varchar(500) = '</FONT><FONT SIZE=2 FACE="Arial">[email protected]</FONT><FONT SIZE=2 FACE="Arial">'
SELECT substring(@str,patindex('%">%',@str),patindex('%</FONT>%',@str))

I'm trying to get email id from @str string.

output should be.

[email protected]

I'm not getting how should i deal with in substring in sql-server?

Upvotes: 0

Views: 1653

Answers (3)

vmvadivel
vmvadivel

Reputation: 1067

This is the approach I would try:

  1. Strip off the HTML tags from the string
  2. Make use of Phil Factors script from PATINDEX Workbench to extract the email address from the string.

Even if the string has multiple @ symbols in it the script would ignore the one which is not part of a email address. Also if there are multiple email address within the string this script would fetch only the first one.

--This script is NOT written by me. I have it in my laptop and at present i don't remember  who created this :(
CREATE FUNCTION [dbo].[StripHTML]( @text VARCHAR(MAX) ) 
RETURNS VARCHAR(MAX) 
AS
BEGIN
    DECLARE @textXML XML
    DECLARE @result VARCHAR(MAX)
    SET @textXML = @text;
    WITH doc(contents) AS
    (
        SELECT chunks.chunk.query('.') FROM @textXML.nodes('/') AS chunks(chunk)
    )
    SELECT @result = contents.value('.', 'varchar(max)') FROM doc
    RETURN @result
END
GO

--Variable declaration & test string
DECLARE @str AS VARCHAR(500) 
SET @str = 'Alpha Beta <FONT SIZE=2 FACE="Arial"> i will be @ gamma</font> one two three </FONT><FONT SIZE=2 FACE="Arial">[email protected]</FONT><FONT SIZE=2 FACE="Arial"> i hate mondays'

--First lets strip HTML out of the given string
--I am storing it into the same variable may be you might want to use another one!
SELECT @str = dbo.udf_StripHTML(@str)

--Extract the Email ID from the given string. 
--Even if there are @ symbol within the string multiple times it ignore the one which is not part of email address
--if there are multiple email ids this script would list the first one ONLY
--Source: http://www.simple-talk.com/sql/t-sql-programming/patindex-workbench/
SELECT  
    CASE WHEN AtIndex=0 THEN '' --no email found
    ELSE RIGHT(head, PATINDEX('% %', REVERSE(head) + ' ') - 1) + LEFT(tail + ' ', PATINDEX('% %', tail + ' '))
    END EmailAddress
FROM 
(
    SELECT RIGHT(EmbeddedEmail, [len] - AtIndex) AS tail,
             LEFT(EmbeddedEmail, AtIndex) AS head, AtIndex
    FROM 
    (
        SELECT 
            PATINDEX('%[A-Z0-9-]@[A-Z0-9-]%', EmbeddedEmail+' ') AS AtIndex,
            LEN(EmbeddedEmail+'|')-1 AS [len], embeddedEmail
            FROM (
                  SELECT @str 
                 ) AS ListOfCompanies (EmbeddedEmail)
    )f
)g;

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24116

If the string "</FONT><FONT SIZE=2 FACE="Arial">" is constant around the email address then always you can try this

DECLARE @str as varchar(500) = '</FONT><FONT SIZE=2 FACE="Arial">[email protected]</FONT><FONT SIZE=2 FACE="Arial">'
SELECT REPLACE(@str,'</FONT><FONT SIZE=2 FACE="Arial">','')

If its not constant 

DECLARE @str as varchar(500) = '</FONT><FONT SIZE=2 FACE="Arial">[email protected]</FONT><FONT SIZE=2 FACE="Arial">'
select substring(@str,patindex('%">%',@str)+2,patindex('%</FONT>%',substring(@str,patindex('%">%',@str)+3,len(@str))))

Upvotes: 3

Madhivanan
Madhivanan

Reputation: 13700

DECLARE @str as varchar(500) = '</FONT><FONT SIZE=2 FACE="Arial">[email protected]</FONT><FONT SIZE=2 FACE="Arial">' 

select substring(col,1,charindex('<',col)-1) as email from
(
    select substring(@str,charindex('">',@str)+2,len(@str)) as col
) as t

Upvotes: 0

Related Questions