CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1029572
user1029572

Reputation: 11

getting name inbetween mail id's using sql

I am using SQL server. In that I have a row value as

cc:[email protected] cc:[email protected] james cc:[email protected]

In the above I have to get the value "james" as the output.

replace(column_name,substring(column_name, CHARINDEX('cc:',column_name), CHARINDEX('.com',column_name)+4),'')

Using the above, I have removed the first occurence of cc:[email protected] and now I have to remove the next two occurence of cc:[email protected]

Thanks in advance

Upvotes: 1

Views: 173

Answers (4)

Dinesh
Dinesh

Reputation: 118

SELECT name
FROM Persons
where emailid='cc:[email protected]'

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

declare @txt varchar(1000) ='cc:[email protected] cc:[email protected] james cc:[email protected]'

;with split as
(
select 1 f, charindex(' ', @txt+ ' ', 2) t
union all
select t+1, charindex(' ', @txt+ ' ', t+1)
from split
where charindex(' ', @txt+ ' ', t+1) > f
)
select substring(@txt, f, t-f) from split
where not substring(@txt, f, t-f) like '%@%'

Result:

james

EDIT:

I changed the sql to fit your exact requirement:

create function f_returnnames(@txt varchar(1000))
returns varchar(1000)
 as
begin
declare @returntext varchar(1000)

;with split as
(
select 1 f, charindex(' ', @txt+ ' ', 2) t
union all
select t+1, charindex(' ', @txt+ ' ', t+1)
from split
where charindex(' ', @txt+ ' ', t+1) > f
)
select @returntext = coalesce(@returntext + ' ', '')  + substring(@txt, f, t-f) from split
where not substring(@txt, f, t-f) like '%@%'

return @returntext
end

go

You can test it here:

select (select dbo.f_returnnames(column_name)) names
from 
(select 'cc:[email protected] cc:[email protected] james cc:[email protected]' column_name) a

Upvotes: 2

Filip Popović
Filip Popović

Reputation: 2655

EDIT: it will work only with .com mail addresses.

You have to do multiple REPLACEs. Use a WHILE loop to detect if there is cc: to be removed. Here is complete scalar function code:

CREATE FUNCTION FN_STRIPCC(@COLUMN_NAME NVARCHAR(200))
RETURNS NVARCHAR(200)
AS
BEGIN
    WHILE (CHARINDEX('cc:', @COLUMN_NAME) > 0 )
    BEGIN
        SET @COLUMN_NAME =  replace(@column_name,substring(@column_name, CHARINDEX('cc:',@column_name), CHARINDEX('.com',@column_name)+4),'')
    END
    RETURN @COLUMN_NAME
END

Then You can use it:

SELECT dbo.FN_STRIPCC('cc:[email protected] cc:[email protected] james cc:[email protected]')

Upvotes: 0

sorpigal
sorpigal

Reputation: 26086

May I presume that what you want is always the non-email-address? Let's say "I want to extract the first string in a space-delimited list of strings which doesn't look like an email address" where "look like an email address" is interpreted as "contains an @". Is that close enough?

create function get_me_that_james(@v nvarchar(max)) returns nvarchar(max) as
begin
    declare @email nvarchar(max),  @pos int;

    set @pos = 0;
    select @email = substring(@v, @pos, charindex(' ', @v, @pos) - @pos)
    while(charindex('@', @email) != 0) begin
        set @pos = charindex(' ', @v, @pos) + 1;
        select @email = substring(@v, @pos, charindex(' ', @v, @pos) - @pos);
    end
    return @email;
end

Then call it

select dbo.get_me_that_james('cc:[email protected] cc:[email protected] james cc:[email protected]');

Upvotes: 0

Related Questions