Solved

TSQL PATINDEX

Posted on 2013-01-19
10
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38796771
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:Brendt Hess
ID: 38802480
Can you provide an example of the output you desire from the above query?
0
 

Author Comment

by:Tech315
ID: 38805839
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806183
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
ID: 38806388
Still getting an error when I run it. Incorrect Syntax
0
 
LVL 143

Expert Comment

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

Author Comment

by:Tech315
ID: 38806663
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38806794
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
ID: 38806880
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
ID: 38807307
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

628 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