January 3, 2011 at 2:47 pm
hi,
I have a string like this
'...the email ids abs@yahoo.co.uk and also cds@gmail.com are my mails ids...'
if i want to extract the first email id from tat string how do i do it.
i tried doing it this way
declare @string varchar(max)
set @string = '...the email ids abs@yahoo.co.uk and also cds@gmail.com are my mails ids...'
SELECT REVERSE(SUBSTRING(REVERSE(@String),CHARINDEX( '@', REVERSE(@String))+1, CHARINDEX(' ', REVERSE(@String),
CHARINDEX( '@', REVERSE(@String)))-CHARINDEX( '@', REVERSE(@String))))+
SUBSTRING(@String, CHARINDEX( '@', @String), CHARINDEX(' ',@String, CHARINDEX( '@', @String))-CHARINDEX( '@', @String))
but the result is not what i expected
Result i got,
thanks in advance
January 3, 2011 at 3:21 pm
Something like this?
DECLARE @string VARCHAR(MAX)
SET @string = '...the email ids abs@yahoo.co.uk and also cds@gmail.com are my mails ids...'
DECLARE @sub VARCHAR(MAX)
SELECT @sub = LEFT(@string,CHARINDEX(' ',@string,CHARINDEX('@',@string,1))-1)
SELECT RIGHT(@sub,LEN(@sub)-CHARINDEX(' ',REVERSE(@sub))-1)
I think this method will be more useful than the usual split string approach for the following reasons:
1) the string is VARCHAR(MAX), therefore decreasing the speed of a split string function
2) there is only the first matched value needed
3) a split string on VARCHAR(MAX) just to eliminate a single value seems a little bit overkill, I'd say
January 3, 2011 at 7:25 pm
What if the string contains an "@" that isn't actually part of an email address?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2011 at 1:26 am
Jeff Moden (1/3/2011)
What if the string contains an "@" that isn't actually part of an email address?
Then we'd need the split string, I guess.
I'd probably use the method stated above to find the first string with an '@' and check if it's a valid e-mail. If not, then I'd go with the split string function.
Depending on the data distribution (how many values will be there with a first @ not being part on e-mail) I might even skip the "quick-test-approach"...
Btw: good catch. As usual.
January 4, 2011 at 7:54 am
Hi Lutz,
I created a function by slightly modifying your query
CREATE FUNCTION [dbo].[udf_TEST_EMAIL] (@String VARCHAR(MAX))
RETURNS @Email TABLE (Email VARCHAR(MAX))
AS
BEGIN
DECLARE @E_mailID VARCHAR(MAX), @delimiter CHAR(1), @sub varchar(max)
SET @delimiter ='@'
WHILE CHARINDEX(@delimiter, @String) != 0
BEGIN
SELECT @sub = LEFT(@String, CHARINDEX(' ',@string,CHARINDEX('@',@string,1)))
SELECT @E_mailID = LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(@sub),CHARINDEX( '@', REVERSE(@sub))+1, CHARINDEX(' ', REVERSE(@sub),
CHARINDEX( '@', REVERSE(@sub)))-CHARINDEX( '@', REVERSE(@sub))))+
SUBSTRING(@sub, CHARINDEX( '@', @sub), CHARINDEX(' ',@sub, CHARINDEX( '@', @sub))-CHARINDEX( '@', @sub))))
INSERT INTO @Email
SELECT @E_mailID
SET @String = REPLACE(@String, @E_mailID,'XXX')
END
RETURN
END
January 4, 2011 at 8:24 am
kevin4u06 (1/4/2011)
Hi Lutz,I created a function by slightly modifying your query
CREATE FUNCTION [dbo].[udf_TEST_EMAIL] (@String VARCHAR(MAX))
RETURNS @Email TABLE (Email VARCHAR(MAX))
AS
BEGIN
DECLARE @E_mailID VARCHAR(MAX), @delimiter CHAR(1), @sub varchar(max)
SET @delimiter ='@'
WHILE CHARINDEX(@delimiter, @String) != 0
BEGIN
SELECT @sub = LEFT(@String, CHARINDEX(' ',@string,CHARINDEX('@',@string,1)))
SELECT @E_mailID = LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(@sub),CHARINDEX( '@', REVERSE(@sub))+1, CHARINDEX(' ', REVERSE(@sub),
CHARINDEX( '@', REVERSE(@sub)))-CHARINDEX( '@', REVERSE(@sub))))+
SUBSTRING(@sub, CHARINDEX( '@', @sub), CHARINDEX(' ',@sub, CHARINDEX( '@', @sub))-CHARINDEX( '@', @sub))))
INSERT INTO @Email
SELECT @E_mailID
SET @String = REPLACE(@String, @E_mailID,'XXX')
END
RETURN
END
Objection!!
You didn't "slightly modify" my code - you turned into a st***ing LOOP!! :sick: :crying:
I strongly recommend to search this site for "DelimitedSplit8K" to find a much better performing solution than what you have right now!
January 4, 2011 at 8:32 am
Thanks lutz..
January 4, 2011 at 8:37 am
Actually i'm trying to solve a challenge posted in one of the websites but was unable to do so.....I seriously want to know that solution cause i have a great use of that code......but unfortunately they dont have the solution yet....
the challenge:
can you please provide with the solution....I'm not going to post the solution in that challenge....its for my reference....
thank you....
January 4, 2011 at 8:39 am
No need to follow the "Notes" in the challenge...you can give me any solution which is best....
January 4, 2011 at 8:43 am
It looks like you're dealing with exactly the same scenario as described in one of the
T-SQL challenges over at beyondrelational.
You might want to have a look at the solutions provided.
January 4, 2011 at 8:50 am
Cross-posting. I'm sorry.
But I just checked over at beyondrelational. The challenge is over. Winners are announced. All codes are available.
In general, I don't consider it as a good idea to post stuff related to an open challenge from "ver there" since there are quite some people around following both (including me 🙂 ).
And once the challenge is closed and winners are announced, then there is no need to post the code here, since it's accessible over there, too. 😉
As a side note: there are situations where the "do it all in one query" approach might not be the best performing solution. But from my point of view that doesn't count for the specific challenge (other than using a CLR, probably...).
January 4, 2011 at 8:52 am
they haven't yet....i need that solution....cause im dealing with the same scenario right now....
January 4, 2011 at 9:19 am
kevin4u06 (1/4/2011)
they haven't yet....i need that solution....cause im dealing with the same scenario right now....
Trust me,they have. 😉
January 4, 2011 at 9:26 am
thanks got it......is there any other way instead of a single select statement.....which is beter than the queries posted in the challenge.
January 4, 2011 at 9:38 am
kevin4u06 (1/4/2011)
thanks got it......is there any other way instead of a single select statement.....which is beter than the queries posted in the challenge.
to quote from a previous post:
...there are situations where the "do it all in one query" approach might not be the best performing solution. But from my point of view that doesn't count for the specific challenge (other than using a CLR, probably...).
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply