This article explains the base functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL. Samples script: Sample Results: Variable Data Results ——————– HELLO WORLD RIGHT Results ————- RLD LEFT Results ———— HEL CHARINDEX Results —————– 6 SUBSTRING Results —————– LO WO Combined Results ——————– HELLO
The following instructions are supposed to return the same results, but sometimes the results can be different. The reason is that SERVERPROPERTY automatically reports changes in the network name of the computer, while @@SERVERNAME don’t. SELECT CONVERT(sysname, SERVERPROPERTY(N’servername’)) GO SELECT @@SERVERNAME When they are run on the default instance they return just the server name, but when run over a named instance they return the server name in the following format: servername/instancename.
Find how many tables in database AdventureWorks have column name like ‘EmployeeID’.
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…
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. Here are some samples:
Following script are very useful to know all the constraint in the database. I use this many times to check the foreign key and primary key constraint in database. This is simple but useful script from my personal archive.
Using the WHERE clause to return on the records in the Employee table where the employees are a manager of some type. This example uses the % character for any number of characters. Using the LIKE keyword to return records from the Person table where users’ first names end in “ary”. This example uses the _ characters to represent a single character. Using the LIKE keyword to return records from the Person table where users’ first names end in…
Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.
A very common question we developer often encounters is what is the current Timezone of the server where SQL Server is installed. It is very easy to get current Timezone of the server where SQL Server is installed. Here is the simple script for the same.
This script gets the datetime value in this format MM/DD/YYYY:HR:MI:SSPM or AM. I could have used the CAST or CONVERT option but the did not reproduce the result that I wanted. There are some option in CONVERT option but they converts into a 24hr format. Here is my trigger for insert event. USE dwdata GO /* —————————————————————————– www.myw0.com, Version 1.0, Creation Date: 11/05/2014 Functionality: Generate Datetime values into a text field —————————————————————————– */ if exists (select * from sysobjects…
