Solved

TSQL PATINDEX

Posted on 2013-01-19
10
428 Views
Last Modified: 2013-01-23
morning experts I have the following and dont know how to place it into my select statement to effect the needed column. I got help from a friend on this one but she isnt around today and need to get it done, probably easy for all of you. It needs to effect the table.Summary, dont know how to insert it into the substring.

Thanks Ahead of time, the code is below

declare @var varchar(max)
set @var = '{\rtf1\ansi\deff0\deftab720{\fonttbl{\f0\fswiss MS Sans
Serif;}{\f1\froman\fcharset2 Symbol;}{\f2\fswiss Tahoma;}{\f3\fswiss Tahoma;}}  
{\colortbl\red0\green0\blue0;\red0\green0\blue255;\red0\green0\blue128;}  
\deflang1033\pard\plain\f2\fs17   \par }'

SELECT      
                   SUBSTRING(INCIDENTS.FILENUM, 4,LEN(INCIDENTS.FILENUM))                                                AS[NUMBER],
                   CONVERT(VARCHAR(4), INCIDENTS.RECEIVED_DT, 112)                                                      AS[YEAR],
                  
SUBSTRING(@var, 1, CHARINDEX('{', @var)-1) + rtrim(right(@var, charindex('}', reverse(@var)) - 1))TABLE.SUMMARY                                                                                                      AS[SUMMARY-TEXT]
                  
FROM         INCIDENTS INNER JOIN
                     
             SUMMARIES ON TABLE.INCNUM = TABLE.INCNUM
0
Comment
Question by:Tech315
10 Comments
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
please clarify your question.. where and why do you need to use "PATINDEX"?


PATINDEX
http://msdn.microsoft.com/en-us/library/ms188395(v=sql.105).aspx
0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
Can you provide an example of the output you desire from the above query?
0
 

Author Comment

by:Tech315
Comment Utility
I have the patindex setup but need to apply it to a column. I dont know how to apply it soley to that column. Right now its set up to point at the @var.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
in your select, you can use the column instead of the variable.

SELECT      
                   SUBSTRING(INCIDENTS.FILENUM, 4,LEN(INCIDENTS.FILENUM))                                                AS[NUMBER],
                   CONVERT(VARCHAR(4), INCIDENTS.RECEIVED_DT, 112)                                                      AS[YEAR],
                  
SUBSTRING(SUMMARIES.SUMMARY, 1, CHARINDEX('{', SUMMARIES.SUMMARY)-1) + rtrim(right(SUMMARIES.SUMMARY, charindex('}', reverseSUMMARIES.SUMMARY)) - 1))TABLE.SUMMARY                                                                                                      AS[SUMMARY-TEXT]
                  
FROM         INCIDENTS INNER JOIN
                     
             SUMMARIES ON INCIDENTS.INCNUM = SUMMARIES.INCNUM 

Open in new window


so, what is the problem?
0
 

Author Comment

by:Tech315
Comment Utility
Still getting an error when I run it. Incorrect Syntax
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
can you please post the exact error?
0
 

Author Comment

by:Tech315
Comment Utility
SELECT  
     
SUBSTRING(INCIDENTS.FILENUM, 4,LEN(INCIDENTS.FILENUM))                                          AS[NUMBER],
CONVERT(VARCHAR(4), INCIDENTS.RECEIVED_DT, 112)                                                      AS[YEAR],                   
SUBSTRING(SUMMARIES.SUMMARY, 1, CHARINDEX('{', SUMMARIES.SUMMARY)-1) + rtrim(right(SUMMARIES.SUMMARY,
charindex('}', reverse(SUMMARIES.SUMMARY)) - 1))SUMMARIES.SUMMARY                        AS[SUMMARY-TEXT]
                  
FROM         INCIDENTS INNER JOIN
                     
             SUMMARIES ON INCIDENTS.INCNUM = SUMMARIES.INCNUM


Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '.'.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
there is as SUMMARIES.SUMMARY too much in there ...
let me also introduce you to the table alias concept to simplify your sql code
SELECT  SUBSTRING(i.FILENUM, 4,LEN(i.FILENUM))  AS[NUMBER]
, CONVERT(VARCHAR(4), i.RECEIVED_DT, 112)  AS[YEAR]
, SUBSTRING(s.SUMMARY, 1, CHARINDEX('{', s.SUMMARY)-1) 
 + rtrim(right(s.SUMMARY, charindex('}', reverse(s.SUMMARY)) - 1)) AS [SUMMARY-TEXT]
FROM   INCIDENTS i
JOIN SUMMARIES s
  ON i.INCNUM = s.INCNUM 

Open in new window

0
 

Author Comment

by:Tech315
Comment Utility
Tried it, almost worked but through this error

Msg 536, Level 16, State 5, Line 2
Invalid length parameter passed to the SUBSTRING function.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
Tried it, almost worked but through this error
One of the rows has a SUMMARY that does not contain "{".

Try adding a condition as follows:
FROM   INCIDENTS i
JOIN SUMMARIES s
  ON i.INCNUM = s.INCNUM
WHERE CHARINDEX('{', s.SUMMARY) > 0
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now