Take substring and use it in multiple where condition in sql

Question

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.

Bill Karwin · Accepted Answer · 2022-08-22 15:43:38Z

2

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.

answered Aug 22, 2022 at 15:43

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sivaramakrishnan Over a year ago

The 2nd option is what I tried and got 1046 database not selected error. After some research, it was the LEN() built-in function causing the problem. Thanks a lot for the answer.

forpas · Accepted Answer · 2022-08-22 16:06:52Z

1

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))

edited Aug 22, 2022 at 16:06

answered Aug 22, 2022 at 16:00

forpas

165k10 gold badges51 silver badges85 bronze badges

2 Comments

Sivaramakrishnan Over a year ago

Can u please elaborate on ur 1 st method. Looks like a short solution. Just not getting how to use it. FYI, i only length of val1, val2 and val4 is 4, 4 and 8. Although all the values will vary in each record.

forpas Over a year ago

@Sivaramakrishnan if @var1='abcd', @var2='efgh' and @var3='qrstuv' then the condition is: WHERE text LIKE 'abcdrfghqrstuv________' which means that text should start with 'abcdrfghqrstuv and after that should have 8 more chars (any chars). Simple as that.

Collectives™ on Stack Overflow

Take substring and use it in multiple where condition in sql

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related