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 |
|
funk.phenomena
Posting Yak Master
121 Posts |
 Posted - 2012-08-20 : 12:59:47
|
| Hi All I have a list of values, for which I need to extract [CODE]MEMO_SYSTEM_TXTChanged plan from AAA201206 TO BBB20. Effective 12/07/2012.Changed plan from AAA20456777 TO BBB207. Effective 12/07/2012.Changed plan from AAA201DFSDF TO BBB20106. Effective 12/07/2012.Changed plan from AAA20120 TO BBB20127. Effective 12/07/2012.[/CODE]I need to use a string function to extract ONLY the NEW plan number above. It would read the characters between the "TO" and "EFFECTIVE". Output would be as follows:[CODE]NEW PLANBBB20BBB207BBB20106BBB20127 [/CODE]I can have it read all the characters from the "TO" onwards, but I don't know how to have it read everything up until the "Effective". Any ideas? THANKS![CODE]SELECT SUBSTRING (MEMO_SYSTEM_TXT,PATINDEX ( '%to%' , MEMO_SYSTEM_TXT ),10) FROM TABLE[/CODE] |
|
|
funk.phenomena
Posting Yak Master
121 Posts |
Posted - 2012-08-20 : 13:22:32
|
| Nevermind, figured it out!SELECT SUBSTRING (MEMO_SYSTEM_TXT,PATINDEX ( '%to%' , MEMO_SYSTEM_TXT )+3,PATINDEX ( '%effective%' , MEMO_SYSTEM_TXT )-PATINDEX ( '%to%' , MEMO_SYSTEM_TXT )-6)FROM TABLE |
 |
|
|
|
|
|
STRING function?