Solved

TSQL PATINDEX

Posted on 2013-01-19
10
452 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
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:bhess1
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 SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

809 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