How to pull a sting of data from a text field

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to pull a sting of data from a text field

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-03-08 : 11:18:47

Morning,

We have a text field called note_text that holds whatever notes someone wants to put in. I have a request to pull part of some data out of it for reporting.

for example one of the notes has this for data:


select Note_Text
from Material
where Material = 'D24324 REV A'

Results for this would be:


Material Master Created [LECLERCJL - 8/31/09 10:10:24 AM]    
QC- Try this does this work. [MAYERL - 3/8/2010 11:04:53 AM]   
Notes may go here as well [MAYERL - 3/8/2010 11:17:00 AM]

I just want to pull the words after the QC- but before the next line of text. Is that possible?

Thanks for any insight.

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:21:37

use SUBSTRING and PATINDEX

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-03-08 : 11:37:16

Excellent! That worked great except I'm getting a little of the next field as well.


SELECT 
patindex('%QC%', Note_Text) as 'start',
charindex(' ', Note_Text, patindex('%QC%', Note_Text)) as 'length',
SUBSTRING ( Note_Text ,PATINDEX('%QC%',Note_Text), charindex(' ', Note_Text, patindex('%QC%', Note_Text)))as reportdata
from Material
where Material = 'D24324 REV A'

It gets me this for reportdata:


start       length      reportdata
62          65          QC- Try this does this work. [MAYERL - 3/8/2010 11:04:53 AM]

N

The "N" should be there. Getting closer though!

Thanks

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:39:11

N should be there or shouldnt be there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-03-08 : 11:49:06

Sorry... should NOT be there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:53:26

[code]SELECT
patindex('%QC%', Note_Text) as 'start',
charindex(' ', Note_Text, patindex('%QC%', Note_Text)) as 'length',
SUBSTRING ( Note_Text ,PATINDEX('%QC%',Note_Text), charindex(' ', Note_Text, patindex('%QC%', Note_Text))-3)as reportdata
from Material
where Material = 'D24324 REV A'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-03-08 : 11:55:57

Thank you so much, that did it.

Have a great day,

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 11:57:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources