Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with Charindex function.. Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 1:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:07 PM
Points: 388, Visits: 2,370
I am trying to capture an email address from the below message..whats the best possible way to do it ..


DECLARE @test VARCHAR(500)
SET @test = 'my email is Test@email.com.au how do i capture just the email'



This is what I have tried but couldn't get to finish it where I can capture the whole email address
SELECT SUBSTRING(@test,CHARINDEX('@',@test),CHARINDEX(' ',@test,CHARINDEX('@',@test))-CHARINDEX('@',@test))

Thanks in Advance


Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #1499872
Posted Monday, September 30, 2013 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 5,438, Visits: 10,139
Chinna

You need to REVERSE the string and then look for the first space after the "@".

John
Post #1499874
Posted Monday, September 30, 2013 1:53 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:37 PM
Points: 1,454, Visits: 136
Hi,

Use this script

DECLARE @test VARCHAR(500) 
SET @test = 'my email is Test@email.com.au how do i capture just the email'

SELECT REVERSE(SUBSTRING(REVERSE(@test),CHARINDEX('@',REVERSE(@test))+1,CHARINDEX(' ',REVERSE(@test),CHARINDEX('@',REVERSE(@test)))-CHARINDEX('@',REVERSE(@test))))
+SUBSTRING(@test,CHARINDEX('@',@test),CHARINDEX(' ',@test,CHARINDEX('@',@test))-CHARINDEX('@',@test))


Post #1499883
Posted Monday, September 30, 2013 6:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 6,872, Visits: 14,185
-- solution
;WITH SampleData AS (
SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL
SELECT 'No email address in this string'
)
SELECT test, x3.p3
FROM SampleData
CROSS APPLY (SELECT p1 = NULLIF(CHARINDEX('@',test,1),0)) x1
CROSS APPLY (SELECT p2 = SUBSTRING(test,(2+p1-CHARINDEX(' ',REVERSE(LEFT(test, x1.p1)))),8000)) x2
CROSS APPLY (SELECT p3 = LEFT(p2,CHARINDEX(' ',p2,1)-1)) x3;

-- workings
;WITH SampleData AS (
SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL
SELECT 'No email address in this string'
)
SELECT *
FROM SampleData
CROSS APPLY (SELECT p1 = NULLIF(CHARINDEX('@',test,1),0)) x1
CROSS APPLY (SELECT p2 = LEFT(test, x1.p1)) x2
CROSS APPLY (SELECT p3 = REVERSE(p2)) x3
CROSS APPLY (SELECT p4 = CHARINDEX(' ',p3)) x4
CROSS APPLY (SELECT p5 = 2+p1-p4) x5
CROSS APPLY (SELECT p6 = SUBSTRING(test,p5,8000)) x6
CROSS APPLY (SELECT p7 = LEFT(p6,CHARINDEX(' ',p6,1)-1)) x7;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1499976
Posted Monday, September 30, 2013 9:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 3,937, Visits: 8,927
Just another possible solution.
SELECT *
FROM DelimitedSplit8K(@test, ' ') x
WHERE CHARINDEX( '@', x.Item) > 0

For the code of DelimitedSplit8K please read the following article:
http://www.sqlservercentral.com/articles/Tally+Table/72993/



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1500081
Posted Tuesday, October 1, 2013 6:50 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
To add onto Luis's suggestion:

WITH SampleData AS (
SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL
SELECT 'Some invalid@email addresses can also be removed like this' UNION ALL
SELECT 'Some invalid @email addresses can also be removed like this' UNION ALL
SELECT 'Some invalid email@. addresses can also be removed like this' UNION ALL
SELECT 'Some invalid email@@address.can can also be removed like this' UNION ALL
SELECT 'No email address in this string'
)
SELECT test, item
FROM SampleData a
CROSS APPLY dbo.DelimitedSplit8K(test, ' ') b
WHERE CHARINDEX('@', item) < CHARINDEX('.', item) AND CHARINDEX('@', item) > 1 AND
CHARINDEX('.', item) < LEN(item) AND LEN(item) - 1 = LEN(REPLACE(item, '@', ''));


Etc.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1500636
Posted Wednesday, October 2, 2013 9:18 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 9:07 PM
Points: 388, Visits: 2,370
Thanks a lot guys...All of the responses were helpful .


Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman
Post #1500972
Posted Thursday, October 3, 2013 8:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
Luis Cazares (9/30/2013)
Just another possible solution.
SELECT *
FROM DelimitedSplit8K(@test, ' ') x
WHERE CHARINDEX( '@', x.Item) > 0

For the code of DelimitedSplit8K please read the following article:
http://www.sqlservercentral.com/articles/Tally+Table/72993/




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse