CODEWITHSUNDEEP
sqlsql-server
buymeapc
buymeapc

Reputation: 11

How to Select Matching Text Before A Specific Character In A Multi-Level SQL Query

I have a SQL Server 2008 database that has 2 tables that connect via ID columns - a PRODUCTS table and a REPORTS table. The REPORTS table has an INCLUDED_FILES column with a TEXT data type that has a block of text like the example below:

ikernel.dll|04/26/2002 06:48:38 PM|0||A||c:\program files\installshield 10.5\setup\build\ikernel.dll  
setup.dll|03/25/2002 04:11:12 PM|0||A||c:\program files\installshield 10.5\support\build\setup.dll  
setup.exe|06/28/1999 05:30:10 PM|0||A||c:\program files\installshield 10.5\support\build\setup.exe

I am trying to search this column for matching file names using wildcards, but I only want to search up to the first "|" pipe character (and ignore the rest of the string).

I currently have a query that does the search, but I do not know how to search just up to the first pipe only - it search the entire string.

select 
    t1.PATH, t1.VERSION, t1.NAME, t1.CREATION_DATE_DISPLAY, t1.CREATION_DATE
from 
    Products.dbo.PRODUCTS as t1
join 
    Products.dbo.REPORT_PATHS as t2 on (t1.REPORT_ID = t2.ID)
where 
    t2.INCLUDED_FILES like '%'+char(10)+'set%'+char(124)+'%';

This query, however will return more results than desired since it will see the word 'setup' in the first line of text.

How can I limit it to search only the file names before the first pipe character as opposed to the entire string?

Upvotes: 1

Views: 114

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56745

Combining everything that you need:

;with t2 As (
    Select *, CAST(INCLUDED_FILES AS VARCHAR(MAX)) As vfiles
    from Products.dbo.REPORT_PATHS
    )
select 
    t1.PATH, t1.VERSION, t1.NAME, t1.CREATION_DATE_DISPLAY, t1.CREATION_DATE
from 
    Products.dbo.PRODUCTS as t1
join 
    t2 on (t1.REPORT_ID = t2.ID)
where 
    LEFT(t2.vfiles, PATINDEX('%|%', t2.vfiles)) 
        like '%'+char(10)+'set%'+char(124)+'%'
;

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

You can do this with the CHARINDEX (to identify the position of the first pipe character) and the SUBSTRING (to only use the part of the string before the first pipe) functions.

Upvotes: 1

Related Questions