How do I only return part of a string with varying lengths in SQL?

Question

I have a table with only 1 column that contains a string. I am trying to only get the email address. How can I do that? I looked at Substring/Ltrim, etc, but I haven't been able to piece together how to extract only part of the string. I am fairly new to SQL. Thank you for your help!

Column1:

John Smith     Email: [email protected]  Terminate:
  
Jacqueline Smith    Email: [email protected]     Terminate:

Answer 1

Assuming the email is prefixed by Email: and does not contain spaces, you can just take all characters after Email: and before the next space (or end of string);

SELECT CASE WHEN CHARINDEX(' ', a.em) <> 0 
            THEN SUBSTRING(a.em, 1, CHARINDEX(' ', a.em) - 1)
            ELSE a.em END email
FROM (
  SELECT SUBSTRING(column1, CHARINDEX('Email: ', column1) + 7, LEN(column1)) em
  FROM mytable
) a

The subquery keeps anything after Email: and the outer query cuts everything trailing the next space (or end of string).

The query assumes that there is an Email: tag, if that's not guaranteed, you'll want to use a WHERE to make sure that only rows that have will be returned.

An SQLfiddle to test with.

Answer 2

I'm making a few assumptions about your data, namely that the characters 'Name:' don't appear before the name and that each line includes the substring 'Terminate:'

In SQL Server, use a combination of PATINDEX, CHARINDEX and SUBSTRING to parse the address from the string in each row. The cursor lets you loop through your table. This will print out all the e-mail addresses in your table. It needs formatting and if you want to search for a particular person's email, you will have to modify the select statement with a WHERE clause. I hope this helps:

declare @strung as nvarchar(255) ,@start as int ,@end as int ,@result as int ,@emailCursor Cursor

set @emailCursor = CURSOR FORWARD_ONLY STATIC READ_ONLY FOR Select yourColumnName from yourTableName

OPEN @emailCursor   
FETCH NEXT FROM @emailCursor INTO @strung
WHILE @@FETCH_STATUS = 0    
    BEGIN                   
        set @start = (select charindex(':',@strung)+1);
        set @end = (SELECT PATINDEX('%Terminate:%', @strung)-1)
        set @result = @end-@start
        set @address = (select SUBSTRING(@strung, @start, @result ) AS eMailAddress)
        print @address
    FETCH NEXT FROM @emailCursor INTO @strung                       
    END

CLOSE @emailCursor              
DEALLOCATE @emailCursor

CHARINDEX returns the position of the first ':' character in your string (the one after EMAIL). I add one to that value to move you past the ':'

PATINDEX returns the beginning position of the substring 'Terminate'

SUBSTRING returns all the character between the starting position [CHARNINDEX(':', @strung)] and the space before 'Terminate' [PATINDEX('%Terminate:%', @strung)]