Format string from a DB

[daveStudent]

I have a large database, approx 50,000 records.
Each record has 14 Fields.

One fo the fields contains data that looks like this-

CCMAIL:360Info, Mailbox at NA18%NOTES:Mailbox 360Info/NA18/CIBC@Exchange%SMTP[email protected]%SNADS:NA18(360INFO)%X400:c=CA;a= ;p=CIBC;o=NA18;s=360Info;g=Mailbox;%XTELEX:360INFO@XTELEX%XLIST:360INFO@XLIST%XFAX:360INFO@XFAX

I want to pull out the SMTP[email protected] from all that data.

Is it faster, and can it be done using an SQL statement? Or will i have to use the following Mid command -

strTemp = Mid(strTemp,InStr(strTemp,"SMTP:")+5,(Len(strTemp)-(InStr(strTemp,"@CIBC.CA")+7)))

strTemp would then be "[email protected]"

thanks

db

[daveStudent]

these are supposed to be : D, without the space

[nswan]

if you did it with a stored procedure then that would be fast (if u r using SQL server),

If you are passing an sql string though it would take the longest as it would need to be passed to and then compilied at the database end before it's run. Therefore if you are using an SQL string i'd do it in code once you get the record back.

[hellswraith]

You need to look into regular expressions.
http://www.vbforums.com/showthread.p...ar+expressions

[daveStudent]

yeah i printed out that doc and started reading it yesterday...
Not exactly an easy read.