Format select statement

I have an application that  stores comments in MS SQL database.

original format :
      {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs17 test work order notes }

desired output:  test work order notes
Not sure hot to go about separating format information from actual notes. Something along the lines everything between \fs17 and }?
Thank you for your help
bearsgoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohammedUCommented:
You try using SUBSTRING AND CHARINDEX functions...
declare @Var Varchar(1000)
select @var = '\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs17 test work order notes }'

select substring(@var, charindex('fs17',@var)+5,  (charindex('}',@var, charindex('fs17',@var)+5 )-2 ) - (charindex('fs17',@var)+5))

0
RimvisCommented:
Hello bearsgone,

Take a look:

Strip those RTF tags away
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90034

Regards,

Rimvis
0
VishnukumarCommented:
Please try this

select left(@string1:=substring(columnName,charindex('fs17',columnName,)+4),instr('}',@string1)-1) from tableName;

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

bearsgoneAuthor Commented:
Thank you for your help, but I am still not sure how would that be implemented in this case where tblSchedules.Notes contains {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs17 test work order notes }

select tblCustomers.CompanyName,(tblJobs.StartTime/36) as StartTime,(tblJobs.JobNumText) as Job,
(tblSchedules.ArriveWindow) as Window, (tblSchedules.Subject) as Summary, tblSchedules.Notes
from tblJobs, tblSchedules, tblCustomers
where tblSchedules.ScheduleID = tblJobs.ScheduleID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblSchedules.TeamID = 36
0
MohammedUCommented:
try this...

select tblCustomers.CompanyName,(tblJobs.StartTime/36) as StartTime,(tblJobs.JobNumText) as Job,
(tblSchedules.ArriveWindow) as Window, (tblSchedules.Subject) as Summary,
-- tblSchedules.Notes
substring(tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5,  (charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 )-2 ) - (charindex('fs17',tblSchedules.Notes)+5))
from tblJobs, tblSchedules, tblCustomers
where tblSchedules.ScheduleID = tblJobs.ScheduleID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblSchedules.TeamID = 36
0
bearsgoneAuthor Commented:
MohammedU:

I get

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'substring'.

for substring(tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5,  (charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 )-2 ) - (charindex('fs17',tblSchedules.Notes)+5))
0
MohammedUCommented:
try the following code...
I am not getting the sytax error...
Make sure there is no missing commas or some thing like that...
select tblCustomers.CompanyName,(tblJobs.StartTime/36) as StartTime,(tblJobs.JobNumText) as Job, 
(tblSchedules.ArriveWindow) as Window, (tblSchedules.Subject) as Summary ,
substring(tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5,  (charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 )-2 ) - (charindex('fs17',tblSchedules.Notes)+5)) as Notes
from tblJobs, tblSchedules, tblCustomers
where tblSchedules.ScheduleID = tblJobs.ScheduleID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblSchedules.TeamID = 36

Open in new window

0
bearsgoneAuthor Commented:
Now I am getting
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
0
bearsgoneAuthor Commented:
Another note to make:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs17 test work order notes }

can change as far as shrift and etc. So instead of Tahoma it might say Arial.

test work order notes can become 50 words as well
0
MohammedUCommented:
This error is mostly CHARINDEX is returning NULL or zero...
First take one row and work it out then try for complete table...
0
MohammedUCommented:
I have tested with the following script and it is working...

create table #test (id int, n Varchar(1000))
insert into #test
select 1, '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs17 test work order notes }'

select substring(n, charindex('fs17',n)+5,  (charindex('}',n, charindex('fs17',n)+5 )-2 ) - (charindex('fs17',n)+5))
from #test
0
bearsgoneAuthor Commented:
Correct. There are empty notes for some records. How would I go about handling empty notes?

maybe use cases, but not sure


Thank you
0
MohammedUCommented:
Use where clause to check NOTES IS NOT NULL OR NOTES <> ''
OR
charindex('fs17',n) >0
0
bearsgoneAuthor Commented:
Now I have code below and get
Msg 206, Level 16, State 2, Line 1
Operand type clash: ntext is incompatible with tinyint


select tblCustomers.CompanyName,(tblJobs.StartTime/36) as StartTime,(tblJobs.JobNumText) as Job,
(tblSchedules.ArriveWindow) as Window, (tblSchedules.Subject) as Summary,
substring(tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5,  
(charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 )-2 ) - (charindex('fs17',tblSchedules.Notes)+5)) as Notes
from tblJobs, tblSchedules, tblCustomers
where tblSchedules.ScheduleID = tblJobs.ScheduleID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblSchedules.TeamID = 36
and Notes IS NOT NULL OR Notes <> 0

0
MohammedUCommented:
Remove  Notes <> 0

0
bearsgoneAuthor Commented:
It ignores tblSchedules.Notes IS NOT NULL  and still shows empty notes
0
MohammedUCommented:
You can try...
NOTES  LIKE '[a-z] %'
OR
charindex('fs17',n) >0 IF each row has the fs17 word in it...
0
bearsgoneAuthor Commented:
I will need to see all the records with empty notes or not, it is just if there are empty notes  for substring(tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5,  
(charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 )-2 ) - (charindex('fs17',tblSchedules.Notes)+5)) as Notes

I get Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

Which is correct since some notes are empty

Is there a way to get all records with empty notes and have charindex ignore empty ones?
0
MohammedUCommented:
select tblCustomers.CompanyName,(tblJobs.StartTime/36) as StartTime,(tblJobs.JobNumText) as Job,
(tblSchedules.ArriveWindow) as Window, (tblSchedules.Subject) as Summary,
case when charindex('fs17',tblSchedules.Notes) >0 and charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 ) >0
then       substring(tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5,  
      (charindex('}',tblSchedules.Notes, charindex('fs17',tblSchedules.Notes)+5 )-2 ) - (charindex('fs17',tblSchedules.Notes)+5))
      else tblSchedules.Notes end as Notes  
from tblJobs, tblSchedules, tblCustomers
where tblSchedules.ScheduleID = tblJobs.ScheduleID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblSchedules.TeamID = 36
and Notes IS NOT NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bearsgoneAuthor Commented:
When I run the code from above, I get  143 records and still get Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function at the same time.
Total count of legit records is 960

It does not seem to process all the records. Seems like it gets  to line 144 which has:
{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}}  {\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\li720\f0\fs17  } and throws an exception

0
MohammedUCommented:
It is hard to troubleshoot when you have the data issue...
0
bearsgoneAuthor Commented:
I think the difference is between two endings

pard\li720\f0\fs17
and
uc1\pard\f0\fs17

Looks like to chokes on pard\li720\f0\fs17
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.