Take substring and use it in multiple where condition in sql-CodePudding

I have a string which has some values concatenated. Like 'Val1val2val3val4'.

I have a query like Select * from table where var1 = Val1 and var2 = val2 and var3 = var3;

Val1 and val2 are length only 4 but val3 may differ in length. Val4 is of length 8 and no use.

I can form query with functions like var1 = substring (text, 1, 4), var2 = substring (text, 5, 8) and for var3 = substring (text, 9, Len(text) - 8).

But the problem is, i have to manually edit replace the value in 3 places in query each time in workbench. Which is a painful task in my case. Is it possible to just put the string in one place and make the SQL automatically take substrings and use in where clause conditions? I have only read access, cannot modify tables. Local variables like @var = Text, throws error code 1046. Hands tied for me. Need ideas if feasible.

CodePudding user response：

You could put the input string into a derived table of one row, and cross-join that to your table:

SELECT ...
FROM (SELECT 'Val1val2val3val4' AS text) AS v
CROSS JOIN MyTable
WHERE var1 = substring(v.text, 1, 4) AND ...

Or you could use a user-defined variable and use it in a subsequent query:

SET @var = 'Val1val2val3val4';

SELECT ...
FROM MyTable
WHERE var1 = substring(@var, 1, 4) AND ...

You mentioned you got a 1046 error, but it's not clear how you were trying to assign it, so I can't guess what happened.

CodePudding user response：

If you are sure that both @var1 and @var2 have length of 4 chars, then all you need is:

WHERE text LIKE CONCAT(@var1, @var2, @var3, REPEAT('_', 8))

If not then:

WHERE text LIKE CONCAT(@var1, @var2, @var3, REPEAT('_', 8))
  AND CHAR_LENGTH(@var1) = 4
  AND CHAR_LENGTH(@var2) = 4;

If the comparison should be case sensitive, change to:

WHERE text LIKE BINARY CONCAT(@var1, @var2, @var3, REPEAT('_', 8))