Clever Geek Handbook

How to return part of a variable length string in SQL?

I have a table with 1 column containing a row. I am trying to get just an email address. How can i do this? I looked at the substring / Ltrim etc., but I was unable to put together the part to extract only a part of the string. I am new to SQL. Thanks for the help!

Column1:

John Smith     Email: John.Smith@987456email.com  Terminate:

Jacqueline Ryan    Email: Jacqueline.Ryan@987456email.com     Terminate:
+3


source to share


3 answers


Assuming the email is prefixed Email:

and contains no spaces, you can simply take all characters after Email:

and before the next space (or end of line);

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 saves anything after Email:

, and the outer query truncates whatever ends in the next space (or end of line).



The request assumes the tag exists Email:

, if not guaranteed you will want to use WHERE

to make sure that only the rows that will be returned will be returned.

SQLfiddle for testing with .

+5


source


I make several assumptions about your data, namely that the "Name:" characters do not appear before the name and each line contains the "Terminate:" substring.

In SQL Server, use the combination of PATINDEX, CHARINDEX, and SUBSTRING to parse the address from a string on each line. The cursor allows you to move around your table. This will print all the email addresses in your spreadsheet. It needs formatting, and if you want to search for a specific person's email, you'll have to change the select statement with a WHERE clause. 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 your column name 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 (after EMAIL). I add one to this value to move you through ':'

PATINDEX returns the starting position of the substring "Terminate"

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

+2


source


http://sqlfiddle.com/#!6/5ce48/8/0

select ltrim(rtrim(substring(column1,patindex(column1,':')+1, len(column1)-patindex(column1,':')-11))) as email
from t;

assumes Terminate: is consistent and first: marks the end of the first characters to be removed.

What it does:

  • It uses SUBSTRING()

    to output a line based on a specific start and end character.
  • To determine the starting character, we look for the first occurrence: using PATINDEX()

    and add 2 1 for the space, 1 to go to the starting character. This gives us the initial potion forSUBSTRING()

  • To determine how many characters SUBSTRING()

    we take in the LEN()

    entire string, subtract from it the length of everything up to the first ':' + 1 and the length of 'Terminate:' (11)

Again, this HEAVILY assumes consistent formatting. If it is not ': space' and 'Terminate:' is not 11 with a space, then it will not work.

+1


source






More articles:


All Articles