Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL PATINDEX

Posted on 2013-01-19
10
Medium Priority
?
480 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

721 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