SQL Server tip : Query a range of characters ?

If you need to query all People from Person table with FirstName start with A, you can easily query like below,

clip_image002

What if you want to list all the people from person table with first name start with A and B? You can combined Like operator with OR and query like below,

clip_image003

But imagine scenario where you need to query all the People with FirstName start with A to M. How will you do that?

Solution is you need to put like operator in the square bracket as below,

clip_image005

If you modify one little thing in above query and replace like operator with equal operator , you will get no error message however you will get no rows returned as well.

SQL Server Tip : . Where to use SQURE Bracket around table name?

Have you ever thought, what if your table name

1. Is exactly as of SQL Server keyword

2. Is having space in between like Student Fav Actor

If you go ahead and query against table Student Fav Actor like below, most likely you will get error message as below,

clip_image001

There may me one more scenario where table name is something like FROM. If you query in usual way, obviously you will be getting expected error message.

clip_image002

So how to query table named [However this is wrong practice to put spaces and use SQL Server keyword as table name] like above?

Very simple is the solution, put table name in square brackets.

clip_image004

You might encounter columns name of table are having spaces or named as keywords. In that case also you need to apply square bracket with that particular column name to work with.

Output clause in SQL Server

Level: Beginners [level 100]

Output   clause gives access to Inserted, updated or deleted rows of table. In SQL Server 2005 this was possible only through Triggers.

Note: In all explanation, I am going to use School Database. You can download script from here

Imagine a scenario that while inserting a value in Person table, for the audit purpose you need to insert value FirstName column and insertion date in audit table. This was possible in SQL Server through Triggers. Whereas in SQL Server 2008 we can achieve this using Output clause  Smile

I created a table called Audit_Person in school database.

image

image

Now while inserting in Person table, I need to insert a row in this table also.

image

In above statement if you notice we are fetching inserted first name using Inserted.FirstName and current date using GetDate and inserting in Audit_Person table.

Let us say you have another scenario in which

  1. You want to delete all the person First Name started with P
  2. Insert First Name of deleted row with deleted date in Audit_Person table

To write above query you can use Output clause.

image

Scripts used in above explanation are below. Feel free to use them  Smile


Create  table Audit_Person
         (PersonName nvarchar(max),
          DateOfEntry date);

Insert into Person(LastName ,FirstName)
    Output Inserted.FirstName,
           GetDate()
    into Audit_Person
    values
		('Dave','Pinal');

Delete from Person
  Output deleted.Firstname ,
          GetDate()
   Into audit_person
 where FirstName like 'P%';

I hope this post was useful. Thanks for reading  Smile