Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardlewis
Starting Member
2 Posts |
 Posted - 2011-10-13 : 09:58:39
|
| Hi, wondering if someone can help me, I need to extract the domain from an email address column in a table in order to count/group the data in a query. Is there a simple way to do this, using for example substr function or an alternative?ie. looking at below I need to derive the Domain column, using Email... Email Domain ------------------------------------------------- amy.smith@abc.com abc.com paul.baker@def.com def.com Any help would be much appreciated. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-10-13 : 10:01:55
|
| declare @str varchar(100)set @str = 'amy.smith@abc.com'select substring(@str,patindex('%@%',@str)+1,100)JimEveryday I learn something that somebody else already knew |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2011-10-13 : 10:28:43
|
| http://beyondrelational.com/blogs/madhivanan/archive/2011/06/20/selecting-domain-name-from-email-address.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-13 : 10:37:37
|
| try the following function for any delimator : (CREATE FUNCTION SplitString ( -- Add the parameters for the function here @myString varchar(500), @deliminator varchar(10) ) RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here [id] [int] IDENTITY(1,1) NOT NULL, [part] [varchar](50) NULL ) AS BEGIN Declare @iSpaces int Declare @part varchar(50) --initialize spaces Select @iSpaces = charindex(@deliminator,@myString,0) While @iSpaces > 0 Begin Select @part = substring(@myString,0,charindex(@deliminator,@myString,0)) Insert Into @ReturnTable(part) Select @part Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0)) Select @iSpaces = charindex(@deliminator,@myString,0) end If len(@myString) > 0 Insert Into @ReturnTable Select @myString RETURN END GO) |
|
|
|
richardlewis
Starting Member
2 Posts |
Posted - 2011-10-17 : 05:44:28
|
| Many thanks all for your replies and help.The embedded patindex/instr solved my problem. |
|
|
|
|
|
|
|
|
Substr with @ delimiter?