If else with multiple variables and criteria

Question

I'm pretty new to SQL having a background in excel VBA and I'm trying to translate a large excel vba script in to SQL, which is giving me some trouble, primarily with if else statements.

Example table below;

Var1____________ Var2______________Var3_________ Var4 34______________Testing_________________________1234D12345678 45______________Something________ not sure_______G098751425 45______________FDS_____________ not sure_______Reference Value 59______________Another__________ 09871345______1459864658787

The first part of my query is as follows; If i want to make a variables value based on the other variables is if-else statement the best way to go?

if Var1 = '34' and Var2 = 'Testing' set NewVar = Label1
else if Var1 = '45' and Var2 = 'Testing' and Var3 = 'not sure' set NewVar = Label2
else if Var1 = '45' and Var2 = 'FDS' and Var4 = ' Reference Value ' set NewVar = Label3

Second part; i've searched for the best way to do an if else statement with multiple criteria Does and work well enough?

if Var1 = '34' and Var2 = 'Testing' set NewVar = Label1

Third part is; Whats the SQL equivalent of a vba like statement? eg the first row, to test if Var4 has four numbers followed by 'D' then followed by 8 numbers. vba looks like this

if Var4 like "####D########"

where the hash represents a number or to test if row 3 starts with a ‘not’

if Var3 like "not*"

* means wildcard

I have 350 combinations of variables that lead to different NewVar values and this will be running over hundreds of millions of lines of data so it needs to be efficient. Is this the right approach or am I stuck in vba mind set and need a whole different approach for SQL?

Many thanks for any help

score 0 · Answer 1 · answered Aug 06 '15 at 00:40

Let me try to address each question:

The first part of my query is as follows; If i want to make a variables value based on the other variables is if-else statement the best way to go?

+

Second part; i've searched for the best way to do an if else statement with multiple criteria Does and work well enough?

I would recommend you to use the SQL case-when syntax, and let the database do the wok for you, something like:

select case 
            when var1 = '34' and var2 = 'Testing' then 'Label1'
            when var1 = '34' and var2 = 'Testing' then 'Label1'
end as newVal from YourTable

Third part is; Whats the SQL equivalent of a vba like statement? eg the first row, to test if Var4 has four numbers followed by 'D' then followed by 8 numbers.

in SQL the wild card single char operator '_' would work like the vba one.

select column1 from your table where column1 like '____D________'

where the hash represents a number or to test if row 3 starts with a ‘not’

in SQL the most common wild card operator is '%'.

select column1 from your table where column1 like 'not%'

Furthermore, give w3schools website a visit, you will learn a little more about SQL syntax.

If else with multiple variables and criteria

1 Answers1