如何使用Sql解决这个问题？ [英] How to Solve This Using Sql ?

问题描述

Stdid	Firstname		Email
1	Vivek	         	[e-mail]
2	Pankaj	        	[e-mail]
3	Amit	         	[e-mail]
4	Manish		        [e-mail]
5	Abhishek		    [e-mail]

I need my result like 

Stdid	Firstname       part1       part2   part3
1	Vivek	          vivek         @abc     .com
2	Pankaj            pankaj       @gmail    .com
3	Amit	          amit         @yahoo    .com
4	Manish		      manish       @hotmail  .com
5	Abhishek          abhishek     @abcd     .com

part1可以关闭许多字符，因此它必须在@之前分析单词，第二部分必须关闭@vlowed by domain name直到之前。单独和'.com'必须始终在第三部分中重新认识

The part1 can be off many charcters so it must analyse the words before @ and second part must be off @followed by domain name till before. alone and '.com' must always be recoganised in third part

推荐答案

尝试：

Try:

SELECT  stdid, 
        Firstname, 
        SUBSTRING(Email, 1, CHARINDEX('@', Email,1) - 1) AS Part1,
        SUBSTRING(Email, CHARINDEX('@', Email,1), CHARINDEX('.', Email,1) - CHARINDEX('@', Email,1)) AS Part2,
        SUBSTRING(Email, CHARINDEX('.', Email,1), 9999) AS Part3
FROM MyTable

http：// stackoverflow.com/questions/21883080/how-to-split-an-email-address-into-its-parts [ ^ ]

你好，

您可以使用 CHARINDEX 和SUBSTRING 获取所需操作。

Hello ,
You may use CHARINDEX and SUBSTRING to get required op.

declare @name varchar(30)
set @name='[e-mail]'


declare @a varchar(20)
declare @b varchar(20)
declare @c varchar(20)

--get the first part
select @a=substring(@name,1,charindex('@',@name)-1)

--get the second part
select @b=substring(@name,charindex('@',@name),charindex('.',@name)-charindex('@',@name))
--get the third part

select @c=substring(@name,charindex('.',@name),len(@name)-charindex('.',@name)+1)

select @a,@b,@c

这篇关于如何使用Sql解决这个问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！