SQL - How to get the word between special characters in SQL

Question

I am new to SQL and I would like to know how can I find the word between special characters? E.g I would like to get gmail, yahoo from ram@gmail.com,ram@yahoo.com

Answer 1

Try as follows: (for variables)

DECLARE @MAIL VARCHAR(200)='ram@gmail.com'
SET @MAIL=(SUBSTRING(REVERSE(@MAIL),CHARINDEX('.',REVERSE(@MAIL))+1,LEN(@MAIL)))
SELECT (SUBSTRING(REVERSE(@MAIL),CHARINDEX('@',REVERSE(@MAIL))+1,LEN(@MAIL)))

or (for column in table)

CREATE TABLE mails (MAIL VARCHAR(MAX))
INSERT INTO mails VALUES ('RAM@GMAIL.COM')
INSERT INTO mails VALUES ('RAM@YAHOO.COM')

SELECT SUBSTRING((SUBSTRING(MAIL,CHARINDEX('@',MAIL)+1,LEN(MAIL))),0,CHARINDEX('.',(SUBSTRING(MAIL,CHARINDEX('@',MAIL)+1,LEN(MAIL))))) FROM mails

Answer 2

Assuming you are using Oracle:

select regex_replace(email,'.*@([^\.]*).*)', '\1') from my_table;

Answer 3

You can use user defined function to split strings like:

CREATE FUNCTION [dbo].[str__split](
     @str           NVARCHAR(MAX)
    ,@delimiter     NVARCHAR(MAX)
)
RETURNS @split TABLE(
     [str] NVARCHAR(MAX)
)
AS
BEGIN

    INSERT INTO @split(
         [str]
    )
    SELECT 
        [X].[C].[value]('(./text())[1]', 'nvarchar(4000)')
    FROM 
        ( 
            SELECT 
                [X] = CONVERT(XML, '<i>' + REPLACE(@str, @delimiter, '</i><i>') + '</i>').query('.')
        )                   AS  [A] 
    CROSS APPLY 
        [X].[nodes]('i')    AS  [X]([C]);

   RETURN;

END

And then get your data using query:

SELECT 
    LEFT([str], CHARINDEX('.', [str]) -1)  
FROM 
    [dbo].[str__split](@email, '@') 
WHERE  
        CHARINDEX('.', [str]) <>  0;

You can use this in table query, not over one variable only. Also, it works with multiple emails in one string(variable)

Answer 4

Hello There I believe that are business thing so you most likely to but it in the code section not in database section and you can handle it so fast so easy however

SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount 
FROM   dbo.email 
WHERE  LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC

and if you need more description you can find it on this site

http://blog.sqlauthority.com/2011/06/18/sql-server-selecting-domain-from-email-address/

Answer 5

Oracle

select regexp_substr(email,'@([^.]+)',1,1,'',1) from mytable