SQL: the power of left, right and IN

Tweet

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!

CSS: Less!

Tweet

I don't know if you are already aware of LESS. If not, you will probably find it quite interesting. In fact if you're programming using CSS a lot, LESS is a powerful add on which will make things easier for you.

"LESS extends CSS with dynamic behavior such as variables, mixins, operations and functions. LESS runs on both the client-side (Chrome, Safari, Firefox) and server-side, with Node.js and Rhino"

If you are intrigued by LESS and don't know about it already, please follow me...

ASP: how to create a reusable function to query databases

Tweet

Today we are going to create something probably out of the ordinary. We will create two reusable functions (with parameters) in order to query a database.
The first function will open a recordset and execute a query. It will accept 3 parameters: recordset name, connection and query string.
The second function will close the recordset. It will accept 1 parameter: recordset name.

In order to use the parametrised functions, we will benefit from the Execute() VBScript function.
So, before building the two functions, let's try to understand Execute().

ASP: call functions and subroutines

Tweet

With ASP, one of the main features we do like a lot is the possibility of creating procedures. There are two types of procedures: sub and functions.
Subroutines have a specific syntax:

<%
sub sub_name()
... ...
end sub
%>

and they don't output a result.

On the other hand, functions have the same syntax, but they output a result:

<%
function func_name()
... ...
end function
%>

Let's see how they work!

SQL Server: basic functions (part 9) - Substring

Tweet

One of the most interesting Sql function, when working with strings, is Substring. Basically, it returns a part of a string, given the starting point and the length (number of characters).

The syntax
Let's see Substring syntax:

SUBSTRING ( expression , start , length )

In the function we have to declare:

1. expression, which is the string we want to manipulate;
2. start, which is an integer or bigint indicating where to start the extraction. If it is 0 (zero), the starting point will be at the beginning of the expression -1 (see below in "examples"). If start is greater than the length of expression, the function will return a zero-length expression;
3. length, which is a positive integer or bigint indicating how many characters to consider from the start (starting point).

SQL Server: basic functions (part 8) - @@IDENTITY

Tweet

In this post I will introduce you a very tricky and useful function called @@IDENTITY. The function returns the last inserted identity value. As you may already know, the identity column in a Sql Server table is a numeric column that auto increments when a new value is inserted in the table itself. To put it simply, it is the ID column in your table. Now, what is the practical use of @@IDENTITY?

SQL Server: basic functions (part 7) - IsNull, IsNumeric, IsDate

Tweet

This is the seventh part of the Sql Server basic functions series. When I started it, I couldn't think of going that far. Honestly, I thought I would write about some functions, however, after a while, I started to understand that leaving other functions out would be unfair. That's why I would like to write about IsNull, IsNumeric and IsDate... and maybe some other functions in the near future.

SQL Server: basic functions (part 6) - Coalesce

Tweet

Have you ever thought "what is COALESCE afterall?". The first time I encountered it, I thought it was a terrible name for a function. But, considering that a normal dictionary will define "coalesce" as "Come together and form one mass or whole" or "Combine (elements) in a mass or whole", that might shed a little light and help us understand what's behind the function.

SQL Server: basic functions (part 5) - Aggregation

Tweet

Aggregate functions are used in SQL Server queries to perform operation on a group of values and returns one single value as result. Aggregate functions are used with the SELECT statement, with the GROUP BY and the HAVING clauses.
There are different aggregate functions, however the most important are:

• Count
• Sum
• Avg
• Min
• Max

And those are the functions we are going to see here.

SQL Server: basic functions (part 4) - Dates

Tweet

Dates! Working with dates in Sql Server queries might be fun... or not... It's a fact that sooner or later everybody will end up dealing with date manipulation. Sql Server comes with different date functions which can help us through the process. In this article we are going to see:

• datepart
• day, month, year
• datename
• getdate
• dateadd
• datediff

SQL Server: basic functions (part 3) - Cast and Convert

Tweet

Following the other 2 parts of the series, in this short article we are going to see how to convert data in a query.

Cast and Convert: why 2 functions?
Cast and Convert are two functions used to convert data. I've always been confused by the fact that there are actually 2 functions to - apparently - perform the same operation. First of all, I must say that CONVERT can be used only in Sql Server while CAST derives from the ANSI standard. Wow! And so? CONVERT is undoubtedly more flexible while CAST is in a way a standard, it is more portable (it works in almost in any db environment), but it is less powerful. On top of that, CAST is preferably used when converting between decimal and numeric values to preserve the number of decimal places.

SQL Server: basic functions (part 2) - The Case function

Tweet

This is the second part of series of posts on basic SQL functions. In the first part we saw right, left, substring, rtrim, ltrim and len.
In this post we are going to analyse the CASE function.

SQL Server: basic functions (part 1) - Manipulating strings

Tweet

In Sql Server, functions are special commands that perform some kind of operation during the execution of queries. They are used to manipulate results, refine selections, filter data and so on. Some of these functions are really handy and should be used in the query itself because, manipulating data at query level is surely better than do it afterwards.
I would like to gather some functions in some posts, just as quick reference with examples. I understand that the functions here examined are just a tiny selection of all the available commands, however we need to start from somewhere, don't we?
The functions we will see in this post are:

• right, left and substring,
• rtrim and ltrim,
• len.