SQL - How to query between delimiters in a field

Question

I have a very poorly designed database i have inherited which i will be redesigning soon. However in the meantime i need to be able to create a query that searches this data correctly. I already know how to search it simply and i have a query that searches it but i would like to return more accurate results.

Ill start by explaining what i am working with. The SQL Column contains data in the following format:

Door Cat Car|Computer Paper Car|House Door Computer|

Where | is a delimiter between items (stored in a single field. Hmmm... ). There can be 0 to any number of items in the field each delimited by the |, in each row of the table. It's essentially a database within a database field...

My existing query searches this fine but say i searched for both Cat and Computer in the same query it would return the above row as a result as both words occur in the value. What i am looking to achieve is for it to only return the row above if both words occur between any of the delimited items in that value. Which in the above sample data, searching for Cat and Computer would not return this row as they do not appear together between the delimiters.

I know how messy this is, but its my only choice until i can completely overhaul this database. Does anyone know how i can create some kind of select query or some command that will help me to break this down, without me having to perform the regular query, split it by the delimiter,dump it into an array and then filter it further in my code?

Split the row along the delimiter and store the parts in a table variable.Query the table variable for the search string. Here is a way to split the string http://stackoverflow.com/questions/20698318/how-to-split-a-string-into-variables-in-sql — nobody, Feb 25 '16 at 05:58

Shiju Shaji · Answer 1 · 2016-02-25T08:36:59.890

is this what you are looking for..

declare @table table
(col nvarchar(500))

declare @val nvarchar(500)='Door Cat Car|Computer Paper Car|House Door Computer|'

while (charindex('|',@val)>0)
begin
    insert into @table
    select substring(@val,1,charindex('|',@val)-1)

    set @val=substring (@val,charindex('|',@val)+1,len(@val))

end

select * from @table 
where col like '%car%'

**Result**
Computer Paper Car
Door Cat Car

EDI

DECLARE @SampleTable TABLE
    (
      id INT ,
      entries VARCHAR(100)
    );

INSERT  INTO @SampleTable
        ( id, entries )
VALUES  ( 1, 'Door Cat Car|Computer Paper Car|House Door Computer|' ),
        ( 2,
          'Hat Printer Door|Computer|Boat Paper Phone Pen Computer|Monitor Book|' ),
        ( 3, 'Robot|Cat Computer Hat|' ),
        ( 4, 'Light Board|Monkey|' );




DECLARE @table TABLE ( id INT, col NVARCHAR(500) )

DECLARE @sampleTable2 TABLE
    (
      id INT ,
      entries VARCHAR(1000)
    ) 
DECLARE @val NVARCHAR(500)

INSERT  INTO @sampleTable2
        SELECT  *
        FROM    @SampleTable
        WHERE   entries LIKE '%cat%'
                AND entries LIKE '%Computer%'

DECLARE @id_min INT
DECLARE @id_max INT

SELECT  @id_min = MIN(id) ,
        @id_max = MAX(id)
FROM    @sampleTable2

WHILE ( @id_min <= @id_max ) 
    BEGIN
        SELECT  @val = entries
        FROM    @sampleTable2
        WHERE   id = @id_min
        WHILE ( CHARINDEX('|', @val) > 0 ) 
            BEGIN
                INSERT  INTO @table
                        SELECT  @id_min ,
                                SUBSTRING(@val, 1, CHARINDEX('|', @val) - 1)

                SET @val = SUBSTRING(@val, CHARINDEX('|', @val) + 1, LEN(@val))

            END
        SET @id_min = @id_min + 1
    END

SELECT  *
FROM    @table
WHERE   col LIKE '%cat%'
        AND col LIKE '%Computer%'

Here you will get the id and the matching results.

Using than ID you can get the original row.

What if i was looking to return just the original row containing all the text and not specifically the matching substrings in the field — MayneIT, Feb 25 '16 at 06:37
Do you want to return "'Door Cat Car|Computer Paper Car|House Door Computer|'" ? What exactly you want to return when serached for "car"? — Shiju Shaji, Feb 25 '16 at 06:39
Sorry the Sample Data is just the field i wanted to search. I should have given some more information. CREATE TABLE SampleTable (`id` int, `entries` varchar(100)) ; INSERT INTO SampleTable (`id`, `entries`) VALUES (1, 'Door Cat Car|Computer Paper Car|House Door Computer|'), (2, 'Hat Printer Door|Computer|Boat Paper Phone Pen Computer|Monitor Book|'), (3, 'Robot|Cat Computer Hat|'), (4, 'Light Board|Monkey|') ; I would want to return the id of any row that contains some given values. For example if someone searched for both Cat and Computer, it would return id=3 (not 1 and 3) — MayneIT, Feb 25 '16 at 07:01

score 0 · Answer 2 · answered Feb 25 '16 at 06:02

compose a derived table to splitted the values to be excluded and values to be lookup into two column.
then query and look for the values exists in the included column and exclude values in the excluded column

DECLARE @tbl TABLE (
 id INT
 ,vals NVARCHAR(max)
 )

INSERT @tbl
SELECT 1
 ,'Door Cat Car|Computer Paper Car|House Door Computer|'
 INSERT @tbl
SELECT 2
 ,'Door dog Car'
 INSERT @tbl
SELECT 3
 ,'tina|tuna|tona|computer|hana'

SELECT *
FROM (
 SELECT id
  ,CASE 
   WHEN (charindex('|', vals) > 0)
    THEN substring(vals, charindex('|', vals), len(vals))
   ELSE vals
   END AS vals
  ,CASE 
   WHEN (charindex('|', vals) > 0)
    THEN substring(vals, 0, charindex('|', vals))
   ELSE vals
   END AS exclude
 FROM @tbl
 ) AS tb
WHERE (
  tb.vals LIKE '%tuna%'
  OR tb.vals LIKE '%computer%'
  )
 AND (
  tb.exclude NOT LIKE '%tuna%'
  OR tb.vals NOT LIKE '%computer%'
  )

SQL - How to query between delimiters in a field

2 Answers2