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 |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2009-11-30 : 06:51:56
|
| How do you extract one string from another? For example, how would you extract an email address from a sentence?declare @s as varchar (500)set @s='My email address is BobJones@mysite.com and that''s it.'I want the select statement to return: BobJones@mysite.com.But I need to indentify an email address first. All I know is that I'm looking for an email address. But I don't know what the email address is.So I can look for the @ sign and then I can write a function to give me the location of the last space before the @ sign and work it out like that.Is there a better way to do it? In one line? Without a function? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2009-11-30 : 07:15:15
|
| Maybe this is not the best way but this maybe one of the way.DECLARE @x AS VARCHAR (500)SET @x='My email address is BobJones@mysite.com and that''s it.'DECLARE @xml AS XMLSET @xml= '<i>' + REPLACE(@x,' ','</i><i>') + '</i>'SELECT * FROM (SELECT x.i.value('.','varchar(40)')AS mail FROM @xml.nodes('//i')x(i))t WHERE mail LIKE '%@%'PBUH |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2009-11-30 : 08:18:15
|
not bad!quote:
Originally posted by Idera
Maybe this is not the best way but this maybe one of the way.DECLARE @x AS VARCHAR (500)SET @x='My email address is BobJones@mysite.com and that''s it.'DECLARE @xml AS XMLSET @xml= '<i>' + REPLACE(@x,' ','</i><i>') + '</i>'SELECT * FROM (SELECT x.i.value('.','varchar(40)')AS mail FROM @xml.nodes('//i')x(i))t WHERE mail LIKE '%@%'PBUH
|
|
|
|
|
|
|
Find a string