SQL Cascading search

Question

SQL Cascading search

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Good morning!

I have a question, I need to perform a cascade search with a string in the following 5 scenarios:

Scenario1: string = 'value1 value2 value3 value4 value5'
  Scenario2: string = 'value1 value2 value3 value4'
Scenario3:   string = 'value1 value2 value3'
Scenario4: string = 'value1   value2 '
Scenario5: string =' value1 '

The search must be cascaded in a table that contains a field for each value, being more or less like this:

For scenario 1:

SELECT * FROM 'Tabla'<br>
WHERE 'Campo1' = 'valor1'<br>
  AND 'Campo2' = 'valor2'<br>
  AND 'Campo3' = 'valor3'<br>
  AND 'Campo4' = 'valor4'<br>
  AND 'Campo5' = 'valor5'<br>

For scenario 2:

SELECT * FROM 'Tabla'<br>
WHERE 'Campo1' = 'valor1'<br>
  AND 'Campo2' = 'valor2'<br>
  AND 'Campo3' = 'valor3'<br>
  AND 'Campo4' = 'valor4'<br>

And so on ...

The question is: in what way can I separate that value chain to encompass the 5 scenarios that will be variable?

Could the functionality of 'full-text search' serve me to perform the searches on the table using the string directly as a variable in the query?

I hope you can support me, thank you.

sql-server-2012

asked by D. Cortez 10.08.2017 в 17:38

source

2 answers

0

As I see your string variable can contain up to 5 different values, if so a trick that can help you would be to concatenate the left side of the condition to compare it with your variable, ie:

SELECT * FROM 'Tabla'
WHERE 'Campo1' + ' ' 
    + 'Campo2' + ' ' 
    + 'Campo3' + ' ' 
    + 'Campo4' + ' ' 
    + 'Campo5' like '%' + string + '%'

With this you would not worry about breaking down your string, but rather about concatenating your columns correctly.

Cons:

If the order of the values in the 'string' is changing, it will not be enough to concatenate the columns in a single order.
The performance will be affected a bit since the condition does not work on indexed columns.

I hope I have helped, greetings.

answered by 10.08.2017 в 18:56

how to avoid exceptions in firebird Multiple MySQL queries (msqli) in a php file

score 0 · Accepted Answer

If I understood correctly, you can use something like this:

DECLARE @v1 VARCHAR(10), @v2 VARCHAR(10), @v3 VARCHAR(10), @v4 VARCHAR(10), @v5 VARCHAR(10), @var INT, @x VARCHAR(1000), @temp VARCHAR(100)

SET @x = 'COHD910745GV1 123211471 910709 DFSDC412 SDFSD3210'
SET @var = 0

WHILE (CHARINDEX(' ', LTrim(RTRIM(@x)))>0)
BEGIN 
    SET @var= @var + 1
    SET @temp = SUBSTRING(@x, 1, CHARINDEX(' ', LTrim(RTRIM(@x))))
    SET @x = LTRIM(RTRIM(REPLACE(@x, @temp, '')))

    if @var = 1
        SET @v1 = @temp
    if @var = 2
        SET @v2 = @temp
    if @var = 3
        SET @v3 = @temp
    if @var = 4
        SET @v4 = @temp
END

if @v1 is NULL
    SET @v1 = @x
else
    IF @v2 is NULL
        SET @v2 = @x
    ELSE
        if @v3 is NULL
            set @v3 = @x
        ELSE
            IF @v4 is NULL
                SET @v4 = @x
            ELSE
                SET @v5 = @x

SELECT * FROM 'Tabla'
WHERE (Campo1 is null or Campo1 = @v1)
AND (Campo2 is null or Campo2 = @v2)
AND (Campo3 is null or Campo3 = @v3)
AND (Campo4 is null or Campo4 = @v4)
AND (Campo5 is null or Campo5 = @v5)

And so you make the where is conditional, if you initialize the value that filter will be made, if all the values are null, it will return all the records.