Select only characters in SQL

Question

I have strings in a database like this:

firstname.lastname@email.com

And I only need the characters that appear after the @ symbol and before (.) symbol i.e. (email) from the above example

I am trying to find a simple way to do this in SQL.

Related: http://stackoverflow.com/questions/20984617/how-to-get-string-between-two-characters — DenStudent, Apr 13 '17 at 13:43
A similar Q&A can be found [here](http://stackoverflow.com/a/40425080/7812927).
Hope this helps. — BigMacMenu, Apr 13 '17 at 13:44

score 2 · Accepted Answer · answered Apr 13 '17 at 13:48

Do this:

use [your_db_name];
go

create table dbo.test
(
    string varchar(max) null
)
insert into dbo.test values ('firstname.lastname@email.com')

select 
    string,
    substring(
        string, 
        charindex('@', string, 0) + 1, 
        charindex('.', string,  charindex('@', string, 0)) - charindex('@', string, 0) - 1
    ) as you_need
from dbo.test

score 1 · Answer 2 · answered Apr 13 '17 at 13:43

String manipulations are such a pain in SQL Server. Here is one method:

select t.*,
       left(en.emailname, charindex('.', en.emailname + '.') - 1)
from t outer apply
     (select stuff(email, 1, charindex('@', email + '@'), '') as emailname) en;

That that in the charindex() calls, the character being searched for is placed at the end of the string. This allows the code to work even for malformed emails -- it returns an empty string when the email is not of the form '%@%.%'.

Vikrant · Answer 3 · 2017-04-13T13:53:49.130

1

DECLARE @str varchar(50) = 'firstname.lastname@email.com';
SELECT LEFT(
         RIGHT(@str, LEN(@str) - CHARINDEX('@', @str))
         ,CHARINDEX('.', RIGHT(@str, LEN(@str) - CHARINDEX('@', @str))
       ) - 1) AS OUTPUT

Above query gives only domain-name from Email. The query can be applied for column in a table

edited Apr 13 '17 at 13:53

answered Apr 13 '17 at 13:48

Vikrant

4,920
17
48
72

score 1 · Answer 4 · answered Apr 13 '17 at 13:51

1

DECLARE @col char(200) set @col = 'firstname.lastname@email.com'

SELECT SUBSTRING(@col, LEN(LEFT(@col, CHARINDEX ('@', @col))) + 1, LEN(@col) - LEN(LEFT(@col, CHARINDEX ('@', @col))) - LEN(RIGHT(@col, LEN(@col) - CHARINDEX ('.', @col))) - 4);

answered Apr 13 '17 at 13:51

Sky

31
5

score 1 · Answer 5 · answered Apr 13 '17 at 13:54

1

Try This:-

DECLARE @Text varchar(100)
SET @Text = 'firstname.lastname@email.com'
SELECT SUBSTRING(STUFF(@Text, 1, CHARINDEX('@',@Text), ''), 0, 
CHARINDEX('.', STUFF(@Text, 1, CHARINDEX('@',@Text), '')))

Result:-

email

answered Apr 13 '17 at 13:54

ahmed abdelqader

3,409
17
36

score 0 · Answer 6 · answered Apr 13 '17 at 13:52

DECLARE @myStr varchar(100) = 'firstname.lastname@email.com'

SELECT 
SUBSTRING(SUBSTRING(@myStr,CHARINDEX('@',@myStr)+1,LEN(@myStr)-CHARINDEX('@',@myStr)+1),0,CHARINDEX('.',SUBSTRING(@myStr,CHARINDEX('@',@myStr)+1,LEN(@myStr)-CHARINDEX('@',@myStr)+1)))

That can be useful but I really recommend you to build user defined function in C#/Visaul basic they could be much more faster that this.

LukStorms · Answer 7 · 2017-04-13T15:47:59.207

Using charindex, len and reverse to search for the positions of the @ and the last dot.
And substring to get the name based on those positions:

create table test (id int identity(1,1), email varchar(60));

insert into test (email) values 
('jane.doe@email.com'),
('not an email'),
('@invalid.email.xxx'),
('john.doe@longer.domainname.net');

select *, 
 (case 
  when email like '[a-z]%@%.%' 
  then substring(email, 
          charindex('@',email)+1,
          len(email) - charindex('@',email) - charindex('.',reverse(email))
               )
  end) as email_domain_without_extension 
from test;

The CASE WHEN is used to return NULL when it's not an email (instead of an empty string).

Select only characters in SQL

7 Answers7