As the title mentions, in this short article I will show you how to benefit from the use of the left and right SQL functions together with the IN operator.
Before doing so, we need to understand what are the two functions and what the operator is doing.
Please follow me and see what we can do!
The left and right functions
The two functions work perfectly when we need to isolate part of a string. They return the left or right part of a character string with the specified number of characters.
As an example, the syntax for the left function is:
Where strVar is a character string and numChr is a positive number that specifies how many characters we want to get from the string (strVar).
Consider that the same is working for the right function.
The difference between the two functions is that left starts considering the string from the left hand side, while right considers the characters from the right hand side of the string.
As a general example we can create a query like:
If our names (name) in the table (namesTable) are “John”, “Jack” and “Daniel” the results from the above query will be: “Jo”, “Ja” and “Da”. We are getting just the leftmost two characters from the name column.
SELECT LEFT(name, 2)
To simplify the above example we can create two simple queries for the left and right functions:
The first query will return ‘ab’ while the second will return ‘fg’.
SELECT LEFT(‘abcdefg’, 2)
SELECT RIGHT(‘abcdefg’, 2)
The IN operator
The IN operator can be used to determine if a specified value matches any value in a subquery or a list.
Now... now... that might sound complicated, but it’s not.
A common use of the IN operator is in the WHERE clause. We can in fact convert a query like:
To a query like:
WHERE name = ‘John’ or name = ‘Jack’ or name = ‘Daniel’
Isn’t it cool?
WHERE name IN (‘John’, ‘Jack’, ‘Daniel’)
There something more interesting. We can even use another operator... well, actually the opposite operator: NOT IN. As you imagine NOT IN will return the names different from ‘John’, ‘Jack’ and ‘Daniel’:
WHERE name NOT IN (‘John’, ‘Jack’, ‘Daniel’)
All together now!
Now, we can combine the two functions and the IN operator. How?
Let’s say we want to retrieve all records only in specific cases. In T-Sql we can use a conditional statement and execute a query based on that.
The above executes the code inside the BEGIN END only if the table namesTable contains more than 1 record.
IF (SELECT COUNT(*) FROM namesTable) > 1
... ... ...
At the same time we can use something like:
Which returns all records that have a name starting with J or D.
WHERE LEFT(name, 1) IN (‘J’, ‘D’)
Now we can put all together:
The above example is just to explain the logic: if at least a name in the namesTable starts with ‘J’ or ‘D’ the second statement is executed, retrieving all the data from anothe table (otherTable).
IF EXISTS (SELECT * FROM namesTable WHERE LEFT(name, 1) IN (‘J’, ‘D’))
SELECT * FROM otherTable
This is just the beginning, as you may imagine. I leave to your fantasy how to use the above, however, if you want to share your experience just use the comment section below.
In the meantime... happy coding!
(Thanks to Paolo for the hint!)