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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-22 : 09:06:36
|
| Shirley writes "First, I would like to say I am new to SQL programming. I am trying to extract characters from a varchar field. The field looks like this in the first record:X400:c=US;a=Great Lakes;p=Messaging;o=Great Lakes;s=Tucker;g=Chad;%SMTP:John.Doe@nationalcity.com%SNADS:GLAKES(XJXD13A)I need the characters between the two % signs which is the SMTP address. However the field in the next record looks like this:SNADS:GLAKES(XJXS23H)%X400:c=US;a=Great Lakes;p=Messaging;o=Great Lakes;s=Smith;g=John;i=L.;%SMTP:John.Smith@nationalcity.comPlease help.Thanks in advance" |
|
|
dsdeming
479 Posts |
Posted - 2002-04-22 : 10:51:13
|
| If you want to extract the SMTP address, why not extract everything from 'SMTP' on and then trim the garbage off the back end? Maybe something like this:DECLARE @SMTP varchar( 255 )SELECT @SMTP = SUBSTRING( columnname, CHARINDEX( 'SMTP', columnname ), 255 )IF CHARINDEX( '%', @SMTP ) > 0 SET @SMTP = LEFT( @SMTP, CHARINDEX( '%', @SMTP ) - 1 )You'd need to trim any other types of extra info that doesn't start with % off the end as well. |
 |
|
|
|
|
|
extract characters from a field