T/SQL Splint String/Text

One common task performed in database applications is given a full name, how can this be split into the first name and last name.  In this article, three different methods are discussed to perform this task using three different functions in SQL Server.  It is assumed in this article that the full name is just made up of one first name and one last name. Using SUBSTRING Function The first method is the use of the SUBSTRING string function,…

Read More

T/SQL Generate Random Numbers

SQL Server has a built-in function that generates a random number, the RAND() mathematical function.  The RAND math function returns a random float value from 0 through 1.  It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value. To use it, you can simply do a simple SELECT, as follows: SELECT RAND() AS The result generated by this SELECT statement is as follows (note that…

Read More

UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement

In recent times I have seen a developer writing a cursor to update a table. When asked the reason was he had no idea how to use multiple tables with the help of the JOIN clause in the UPDATE statement. However, the easiest and the most clean way is to use JOIN clause in the UPDATE statement and use multiple tables in the UPDATE statement and do the task. Let us see the following example. We have two tables Table 1…

Read More

MySQL TRIM() function

MySQL TRIM() function returns a string after removing all prefixes or suffixes from the given string. Syntax TRIM( str) Arguments Name Description BOTH Indicates that prefixes from both left and right are to be removed. LEADING Indicates that only leading prefixes are to be removed. TRAILING Indicates that only trailing prefixes are to be removed. remstr The string to be removed. FROM Keyword str The actual string from where remstr is to…

Read More

Create an ODBC Connection to Access Database

The Microsoft Access, also known as MSAccess, is a management system database from Microsoft, packaged with Microsoft Office Professional which combines the Microsoft Database Engine with a graphical user interface. In Windows 64bit to check whether the 32-bit Microsoft Access Database Engine drivers are installed, go to C:\Windows\SysWOW64\odbcad32.exe. If on a 32-bit machine, go to C:\Windows\System32\odbcad32.exe to access the utility.  Go to the drivers tab. If the Microsoft Access Driver lists .mdb and .accdb, then the correct drivers are installed….

Read More

SQL Joins

SQL joins are used to combine rows from two or more tables. SQL JOIN An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met. Let’s look at a selection from the “Orders” table: OrderID CustomerID OrderDate 10308 2 1996-09-18 10309 37…

Read More

Querying the tables using JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The…

Read More

Posted in Reference, SQL Tagged Comments Off on Querying the tables using JOIN Keyword