Home

Filtering String-Based Fields

     

Introduction

As you should know already, the values of a certain column can be null or using a value of type char, varchar, or varchar(max), or their variants (nchar, nvarchar, or nvarchar(max)).

   

The primary Boolean operation you can perform on a field consists of checking its nullity. As mentiond already, this operation can be performed by using IS NULL in its expression. Here is an example:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnSelect;
    DataGridView dgvVideos;
    Button btnCreateTableAndRecords;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateTableAndRecords = new Button();
        btnCreateTableAndRecords.AutoSize = true;
        btnCreateTableAndRecords.Location = new Point(12, 12);
        btnCreateTableAndRecords.Text = "Create Table & Records";
        btnCreateTableAndRecords.Click += new EventHandler(btnDatabaseClick);

        btnSelect = new Button();
        btnSelect.Text = "Select";
        btnSelect.Location = new Point(160, 12);
        btnSelect.Click += new EventHandler(btnSelectClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(465, 145);

        Controls.Add(btnSelect);
        Text = "Video Collection";
        Controls.Add(dgvVideos);
        Controls.Add(btnCreateTableAndRecords);
        Size = new System.Drawing.Size(500, 230);
        StartPosition = FormStartPosition.CenterScreen;

        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                              AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void CreateTableAndRecords()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("CREATE TABLE Videos(" +
                               "[Shelf #] nchar(7) null," +
                               "Title nvarchar(50) not null," +
                               "Director nvarchar(50)," +
                               "[Length] int," +
                               "Rating nchar(10)," +
                               "[Year] int);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            MessageBox.Show("A table named \"Videos\" has been created.",
                            "Video Collection",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("INSERT INTO Videos " +
                        "VALUES('DHE-927', 'Two for the Money', 'D.J. Caruso', 123, 'R', 2008)," +
                        "('CGM-683', 'Her Alibi', 'Bruce Beresford', 94, 'PG-13', 1998)," +
                        "('FQT-973', 'Memoirs of a Geisha', 'Rob Marshall', 145, 'PG-13', 2006)," +
                        "('DBT-395', 'Wall Street', 'Oliver Stone', 126, 'R', 2000)," +
                        "(NULL, 'Stealing Harvard', 'Bruce McCulloch', 85, 'PG-13', NULL)," +
                        "('TPH-973', 'A Few Good Men', 'Rob Reiner', 138, NULL, 1992)," +
                        "(NULL, 'The Silence of the Lambs', 'Jonathan Demme', 118, NULL, 1991)," +
                        "('DZV-737', 'The Lady Killers', 'Joel Coen & Ethan Coen', 104, 'R', NULL)," +
                        "(NULL, 'Sneakers', 'Phil Alden Robinson', 126, 'PG-13', 1992)," +
                        "(NULL, 'Annie', 'John Huston', 126, 'G', 1982)," +
                        "(NULL, 'Dave', 'Ivan Reitman', 110, 'PG-13', 1993)," +
                        "('ADR-737', 'Incredibles (The)', 'Brad Bird', 133, 'PG', 2004);",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            MessageBox.Show("A few records have been created.",
                            "Video Collection",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);
        }
    }

    void btnDatabaseClick(object sender, EventArgs e)
    {
        CreateTableAndRecords();

        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    void btnSelectClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE  [Shelf #] IS NULL;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Videos

Videos

As mentioned already, to make the condition easier to read, you should include the expression in parentheses. This would be done as follows:

SELECT ALL * FROM Videos
WHERE ([Shelf #] IS NULL);
GO

When the statement executes, the table would display only the records that don't have a value for the state. On the other hand, to get the records that are not null, you would use IS NOT NULL. Here is an example:

void btnSelectClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
    {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE  [Shelf #] IS NOT NULL;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
    }
}

This would produce:

Videos

Checking for String Equality

Another common operation performed on a field consists of finding out whether it holds a specific value. This is done using the equality "=" operator. Therefore, to find out whether a field holds a certain value, compare it with that value. You must include the value in single-quotes. Here is an example:

SELECT ALL * FROM Videos
WHERE  Rating = N'R';
GO

In a WHERE statement, you can also use the ORDER BY expression to sort a list of records based on a column of your choice. Here is an example:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnOrder;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnOrder = new Button();
        btnOrder.Text = "Select";
        btnOrder.Location = new Point(12, 12);
        btnOrder.Click += new EventHandler(btnSelectClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(465, 145);

        Controls.Add(btnOrder);
        Text = "Video Collection";
        Controls.Add(dgvVideos);
        Size = new System.Drawing.Size(500, 230);
        StartPosition = FormStartPosition.CenterScreen;

        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                           AnchorStyles.Right | AnchorStyles.Bottom;
        ShowVideos();
    }

    void ShowVideos()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE Rating = 'R';",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    void btnSelectClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE Rating = 'R' " +
                               "ORDER BY Director;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

WHERE Condition

WHERE Condition

Checking for String Inequality

To check whether a field doesn't hold a certain value, you can use the <> operator. Here is an example:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnOrder;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnOrder = new Button();
        btnOrder.Text = "Select";
        btnOrder.Location = new Point(12, 12);
        btnOrder.Click += new EventHandler(btnSelectClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(465, 145);

        Controls.Add(btnOrder);
        Text = "Video Collection";
        Controls.Add(dgvVideos);
        Size = new System.Drawing.Size(500, 230);
        StartPosition = FormStartPosition.CenterScreen;

        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                           AnchorStyles.Right | AnchorStyles.Bottom;
        ShowVideos();
    }


    void ShowVideos()
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos;",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    void btnSelectClick(object sender, EventArgs e)
    {
        using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE  Rating <> N'R';",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Checking for String Inequality

Checking for String Inequality

Remember (from Lesson 08) that, besides <>, Transact-SQL also supports the != operator used to perform a comparison for inequality. Therefore, the above statement can also be written as:

SELECT ALL * FROM Videos
WHERE  Rating != N'R';
GO

As an alternative, instead of <> or !=, use the equality operator but precede the expression with NOT. Here is an example:

void btnSelectClick(object sender, EventArgs e)
{
    using (SqlConnection connection =
            new SqlConnection("Data Source=(local);" +
                              "Database='VideoCollection1';" +
                              "Integrated Security=yes;"))
    {
            SqlCommand command =
                new SqlCommand("SELECT ALL * FROM Videos " +
                               "WHERE NOT Rating = N'R';",
                               connection);
            connection.Open();
            command.ExecuteNonQuery();

            SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
            BindingSource bsVideos = new BindingSource();

            DataSet dsVideos = new DataSet("VideosSet");
            sdaVideos.Fill(dsVideos);

            bsVideos.DataSource = dsVideos.Tables[0];
            dgvVideos.DataSource = bsVideos;
    }
}

Checking for String Not Equal

Notice that the result is the same as if only the comparison for equality was used. Of course, you can precede the <> operation with NOT. Here is an example:

SELECT StudentNumber, FirstName, LastName, Gender, ParentsNames
FROM Registration.Students
WHERE NOT (Gender <> 'Male');
GO

In this case, the result would include not the records that are not equal to the value, which would be equivalent to using = and not NOT.

 

Home Copyright © 2010-2011 C#Key