Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

TSQL PATINDEX

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
Tech315
Asked:
Tech315
1 Solution
 
Eugene ZCommented:
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
 
Brendt HessSenior DBACommented:
Can you provide an example of the output you desire from the above query?
0
 
Tech315Author Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Tech315Author Commented:
Still getting an error when I run it. Incorrect Syntax
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please post the exact error?
0
 
Tech315Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Tech315Author Commented:
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
 
Anthony PerkinsCommented:
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now