Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Introduction to Bash Scripting: Conditional Statements


By:   |   Last Updated: 2018-12-20   |   Comments (2)   |   Related Tips: More > SQL Server on Linux

Problem

Since SQL Server now runs on Linux, it would be helpful to understand how to write Linux scripts to help administer SQL Server, similar to what you do with Windows batch scripts.  In this tip we cover how to use conditional logic in the scripts.

Solution

Every programming and scripting language has conditional statements. If there is something in common between Transact SQL, Assembler and batch files it is the presence of conditional statements.  I assume that all readers know what conditional statements are, but if not here is a brief description.

A conditional statement, usually referred as an if..then statement, is a feature of programming languages that performs an evaluation of one or more conditions and according to the evaluation follows a specific execution flow. The conditions are evaluated as true or false. In other words, If conditions are true then some action(s) is (are) executed.

The If..Then Construct

This construct allows us to take a different course of action when some conditions are met in our script. The basic syntax of the If..Then construct is as follows:

if [[ conditional expression1 ]]
then
   expression1 statement1
   expression1 statement2
   ...
elif [[ conditional expression2 ]]
then
   expression2 statement1
   expression2 statement2
   ...
else
   expression3 statement1
   expression3 statement
   ...
fi

The double brackets hold a conditional expression that is tested. If the expression tested is true (or zero which is the numeric value of true) then expression1 is executed. But if the conditional expression is evaluated to false (or one) then expression1 is overlooked and the execution follows to the next elif (else if) evaluation; or in the last instance (i.e. if none of the conditions were met) runs the statements in the else clause.

A word of advice, you may see scripts that uses single brackets "[" instead of double brackets "[[". The difference between using single or double brackets is that double brackets are a bash construct that is not POSIX compatible. The single bracket is POSIX compatible, but it is prone to mistakes. Here is a link to a page that explains this differences with further details in case you are curious http://mywiki.wooledge.org/BashFAQ/031.

But things are not that easy when you face it for the first time. For example, consider the following code which is a perfectly valid script and sample.sh is the file name of the script:

#!/bin/bash

if [[ -e sample.sh ]]
then 
   echo "File sample.sh Exists"
else
   echo "File sample.sh doesn't Exists"
fi

If you never worked with a bash script, or even if you have worked as a programmer I am sure that the conditional expression of the previous script "-e sample.sh ” is something disturbing. In order to understand this; think that the content between brackets is an expression to be tested, in this case the existence of a file named sample.sh. There is a command on Linux named "test”. Long story short, Bash treats the content between brackets as the arguments of the test command (if you want a more detailed explanation which is far from the scope of this tip, you should take a look at the previous link which I am repeating here http://mywiki.wooledge.org/BashFAQ/031).

In other words, "[[ -e sample.sh ]]” is like executing "test -e sample.sh”. But if you run the test command you won't see any result unless you run the "echo $?” command which is used to display the exit status of the command previously executed.

Let's consider the following example when I run the test command with a simple numeric comparison.

test 1 = 1
echo $?
test 1 = 2
echo $?

On the next screen capture you will see the output of the execution of the previous code.

Comparing numbers with test command.

Now let's change the "test” to double square brackets and see what happens:

[[ 1 = 1 ]]
echo $?
[[ 1 = 2 ]]
echo $?
Comparing numbers using square brackets.

As you can see on the previous screen capture the results are identical.

Test Expressions

On the next table I included the test expressions that you can use with IF statements. Notice that there is a blank space after the opening double brackets and another one before the closing double brackets.

Test Expression Description
[[ -a Filename ]] Checks if Filename exists and if so returns true.
[[ –e Filename ]] Same as above.
[[ -b Filename ]] Returns true if the file exists and is a block device.
[[ -c Filename ]] Returns true if the file exists and is a character device like a serial port.
[[ -d DirectoryName ]] Returns true if the directory exists.
[[ -h Filename ]] Returns true if the file exists and is a symbolic link.
[[ -p Filename ]] Returns true if the file exists and is a named pipe.
[[ -r Filename ]] Returns true if the file exists and is readable (i.e. if you can read it).
[[ -s Filename ]] Returns true if the file exists and its size is bigger than zero.
[[ -w Filename ]] Returns true if the file exists and is writable.
[[ -x Filename ]] Returns true if the file exists and is executable.
[[ Filename1 -nt Filename2 ]] Returns true if Filename1 is newer than Filename2; or if Filename1 exists and its counterpart doesn't exist.
[[ Filename1 -ot Filename2 ]] Returns true if Filename1 is older than Filename2; or if Filename2 exists and Filename1 doesn't exist.
[[-o OptionName ]] Returns true if the shell option "OptionName" is enabled.
[[ -z String ]] Returns true if the length of String is zero.
[[ -n String ]] Returns true if the length of String is greater than zero.
[[ String ]] Same as above.
[[ String1 == String2 ]] Returns true if both strings are equal.
[[ String1 = String2 ]] Same as above.
[[ String1 != String2 ]] Returns true if both strings are not equal.
[[ String1 < String2 ]] Returns true if String1 sorts before String2 lexicographically in the current locale.
[[ String1 > String2 ]] Returns true if String1 sorts after String2 lexicographically in the current locale.
[[ Number1 -gt Number2 ]] Returns true if Number1 is greater than Number2.
[[ Number1 -lt Number2 ]] Returns true if Number1 is less than Number2.
[[ Number1 -ge Number2 ]] Returns true if Number1 is greater than Number2 or both numbers are equal.
[[ Number1 -le Number2 ]] Returns true if Number1 is less than Number2 or both numbers are equal.
[[ Number1 -eq Number2 ]] Returns true if Number1 is equal to Number2.
[[ Number1 -ne Number2 ]] Returns true if Number1 is different to Number2
[[ !Expression ]] Returns true if Expression is false.
[[ (Expression) ]] You can use parenthesis to override operator precedence. Just like in algebra.
[[ Expression1 && Expression2 ]] Returns true if both Expression1 and Expression2 are true. It's a logical AND.
[[ Expression1 || Expression2 ]] Returns true if any of Expression1 or Expression2 are true. It's a logical OR.

If Statement Examples

In the next example, we will test a given file and return a message telling if the file is a block device, a character device or a normal file.

#!/bin/bash

read -p "Enter a file name: " VAR

if [[ -b $VAR ]]
then
   echo $VAR " is a block device"
elif [[ -c $VAR ]]
then
   echo $VAR " is a character device"
else
   echo $VAR " is a normal file"
fi

The next screen capture shows the execution of the previous script entering a character device (/dev/tty, the console); a block device (/dev/sda, the hard disk) and a regular file.

Testing for the type of a file.

The Case Statement

Bash programming also allow us to use Case statements. Unlike the if..then statement, the Case statement in Bash programming is very similar to the case statement in other languages, including Transact-SQL.

The syntax of the Case statement is as follows:

case <Expression> in
   case_1|case_2)  Command_1;
        Command_n;; 
   case_3)  Command_1;
        Command_n;;
   case_n)  Command_1;
        Command_n;;
   *)  Command_1;
     Command_n;;
esac

The Case statement executes one or several groups of statements, depending on the evaluation of a given <Expression> over a set of cases (case_1..case_n). The expression can be an integer or a string. As a consequence, case_1..case_n can also be either an integer or a string. If you need to run a group of statements for different case values of a given <Expression>, you can separate each value with a vertical bar – i.e "case_1|case_2)”-. Additionally you can include a group of statements to be executed when <Expression> does not match with any of the evaluation cases by using an asterisk – i.e. "*)”-.

You can include as many statements as you want in each case group; you should separate each statement with a semicolon (;).  Also you must separate each case block with a double semicolon (;;). If you only use one semicolon (;) then the execution will continue into the next case groups until a double semicolon is found (;;).

Case Examples

The next script shows an example on how to use the case statement with string patterns.

#!/bin/bash

echo "What's your favorite sport?:"
echo "Soccer"
echo "Football"
echo "Volleyball"

read -p "Write your choice: " VAR

case $VAR in
   Soccer)  echo "You chose Soccer";;
   Football) echo "You chose Football";;
   Volleyball) echo "You chose Volleyball";;
esac

On the next screen capture you can see the result of the previous script execution.

Simple Case example with string patterns in bash scripting.

The next script is equivalent to the previous one with the sole difference that it uses a numeric pattern instead of a string.

#!/bin/bash

echo "What's your favorite sport?:"
echo "1 - Soccer"
echo "2 - Football"
echo "3 - Volleyball"

read -p "Write your choice: " VAR

case $VAR in
   1) echo "You chose Soccer";;
   2) echo "You chose Football";;
   3) echo "You chose Volleyball";;
esac

On the next screen capture you can see the result of the previous script execution.

Simple Case example with numeric patterns in bash scripting.

Let's see an example of a case statement in which the case expression evaluation for two different case values executes the same section of code.

#!/bin/bash

echo "Pick the sport that you want to know which ball to use:"
echo "1 - Soccer"
echo "2 - Football"
echo "3 - Volleyball"

read -p "Write your choice: " VAR

case $VAR in
   1|3) echo "This sport uses a rounded ball";;
   2)   echo "This sport uses an oval ball";;
esac

On the next screen capture you can see the output of its execution.

Two different case values executes the same section of code.
Next Steps


Last Updated: 2018-12-20


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, December 28, 2018 - 9:55:03 PM - Daniel Farina Back To Top

Hi Keith,

Please apologize me for my late response. I think that you may be confused with Linux C compiler. In Bash, if you run the "true" command and echo its result you will see that it returns zero. 

true

echo $?

Also if you do the same with the false command you will see that it returns one. 

But if you look at the C include file stdbool.h you will find the next definition, which is like you said.

#ifndef __cplusplus

#define bool    _Bool

#define true    1

#define false   0

#else /* __cplusplus */

Best Regards!


Thursday, December 20, 2018 - 1:35:52 PM - Keith Charlton Back To Top

In your article you say "If the expression tested is true (or zero which is the numeric value of true) then expression1 is executed.". Your comment in brackets is the exact opposite. 1 is taken to be true, but generally any value positive or negative is taken to be True and so any value apart form 0 (zero) is true. 0 (zero) is False.


Learn more about SQL Server tools