Luckily T-SQL has an ISNUMERIC(value) function:

select ISNUMERIC('PostalCode123') -- Returns 0
select ISNUMERIC('123') -- Returns 1

Therefore:

select *
from Customer
where ISNUMERIC(PostalCode) = 1

…returns only the rows where the varchar column PostalCode is numeric.

Imagine you have a user interface with a list of products. On the side of each product you have a delete checkbox and then a delete button below the list. Even though a user can delete multiple products at once, you only want to query the database once. One way to achieve this is by sending a comma separated string with product id’s to a stored procedure. The stored procedure will then loop through the string, grab each id and delete the corresponding row in the product table. Read More »

In this post we will take a look at how to store data in a MS-SQL Server database using ASP.NET. We will start by creating the database table in which we will store the actual file data, and then create a new Web Form with a file upload control and an upload button which will save the uploaded file to the database. After that we need to create a generic HTTP Handler which will be responsible for flushing out the file to the client. The reason to why we will use a HTTP Handler for this is that we don’t need all the functionality that the System.Web.UI.Page provides, so having both performance and simplicity in mind a HTTP Handler is a better choice. Read More »

Imagine you have a Product table, where you need to get a list of say 5 random products. This is how you can do it:

select top 5 *
from Product
order by newid();

So what actually happens is that newid() returns a new GUID/unique identifier for every row in the result set, it then sorts based on the GUID value and then eventually selects the 5 top most rows.

This T-SQL script lets you search through all the columns (nchar, nvarchar, ntext) in a MS-SQL Server database for a given string. Simply change the DbName to your database name and set the @SearchValue to the string you want to search for. The script will return a resultset for each hit, with tablename, column where it found the search-string and all the other columns in that row. Read More »