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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MohammedUConnect With a Mentor Commented:
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
 
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
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

 
VishnukumarCommented:
Please try this

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

Open in new window

0
 
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
 
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
All Courses

From novice to tech pro — start learning today.