substring - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

substring

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-12-02 : 11:27:07

Hi,

I have a table called Candidate with a field called email.
I want to run a query that will return everything after the @ so if the email is xxx@yyy.com the query will return yyy.com.

How can I perform that?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-02 : 11:32:31

use charindex() to find the pos of @ and use substring() or right() to extract it

KH
[spoiler]Time is always against us[/spoiler]

ranganath
Posting Yak Master

209 Posts

Posted - 2007-12-02 : 23:39:00

Hi,

try this

Declare @Str varchar(100)
Set @Str = 'Ranga@yahoo.com'
Select Replace(@str,Left(@str,charIndex('@' , @str)-0),'')

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-03 : 01:04:57

Another way around is
Declare @str varchar(15)
Set @str='xxx@yyy.com'
Select substring(@str,charindex('@',@str)+1,len(@str))

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-12-03 : 04:53:09

Thanks for the replies.
How can I now loop through the entire table and return the values after the @?
The solutions provided returns only one email but i need to do it at once for the entire table.

Many thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-03 : 05:11:29

IF you want to update this value onto another tables field based on a common matching field then

UPDATE c
SET c.<field>=substring(email,charindex('@',email)+1,len(email))
FROM <table_name> t
INNER JOIN Candidate c
ON <joining_condition>

or if you want to extract each value from Candidate alone then:-

Declare @ID int
SELECT @ID=MIN(<idfield>) FROM Candidate
WHILE @ID IS NOT NULL
BEGIN
SELECT substring(email,charindex('@',email)+1,len(email)) FROM Candidate WHERE <idfield>=@ID
SELECT @ID=MIN(<idfield>) FROM Candidate WHERE <idfield> > @ID
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-03 : 05:34:48

quote:
Originally posted by collie

Thanks for the replies.
How can I now loop through the entire table and return the values after the @?
The solutions provided returns only one email but i need to do it at once for the entire table.

Many thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Select substring(col,charindex('@',col)+1,len(col)) from table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources