SQL Training Online

by Leave a Comment

How to Filter using SQL IsDate() Function and Cast

Video: SQL IsDate() Function as a Filter


Using the SQL IsDate() Function in your filter needs a little trick to make it work.

 

In your career you may run into an issue where you have a date stored as a varchar.

When this happens, you may need to use the SQL IsDate in the Where Clause.

You need to first use the SQL IsDate Function to check if the value is a valid date.

Then you can convert the varchar to a date using Cast or Convert.

Even though you have filtered on the SQL Isdate, you might still have a problem with the conversion.

This video shows you how to deal with it.

I don’t mention it in the video, but when dealing with dates, you always have to keep in mind the default date settings of the database.

Cast and Isdate use these defaults and could cause you some trouble if you aren’t aware of them.

Here are the scripts to run the queries that are in the video.

 

create table emp
(
id int primary key identity(1,1)
,emp_name varchar(50)
,emp_birthday varchar(20)
)
GO
insert into emp (emp_name,emp_birthday) values ('Bob','12/12/2001');
insert into emp (emp_name,emp_birthday) values ('Mary','123');
insert into emp (emp_name,emp_birthday) values ('Jill','12/1/2005');
insert into emp (emp_name,emp_birthday) values ('Jim','1/12/1999');
insert into emp (emp_name,emp_birthday) values ('Sue','7/15/2011');
insert into emp (emp_name,emp_birthday) values ('Sally','20120315');
GO

select id,emp_name,emp_birthday
,isdate(emp_birthday)
from emp
where isdate(emp_birthday)=1
and 
case when isdate(emp_birthday)=0 then cast(null as date)
    else cast(emp_birthday as date)
end < '1/1/2006'

Here is the YouTube Video SQL Isdate Function as a Filter with Cast .

by Leave a Comment

Simple Trick to Copy and Paste Web Data into SQL Server

When answering questions on forums or blogs, I often like to put the data into SQL Server where I can make sure my SQL works before I answer a question.

As you can imagine, I don’t want to spend a lot of time importing that data.

So, here is a little trick that you can use to copy and paste and HTML table into a SQL table without having to write insert statements or use the Import/Export wizard.

Video: SQL Server Import Data with Copy and Paste


How to Import Web Data into SQL Server with a simple copy and paste. No special import and export tools needed.

Youtube Video Location: Import HTML Table Data To SQL Server with Copy Paste

 

Here are a few other posts you might enjoy:

SQL Microsoft Excel to Windows Azure SQL in 10 minutes | Troubleshooting a SQL Join | How to Remove Duplicate Rows in SQL Server

 

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline

by Leave a Comment

How to test numeric in SQL Server

Sometimes you will run into a database table that is storing numeric data inside of a varchar or nvarchar column.

This is not recommended, but it does happen.  IsNumeric SQL Server

Normally you would store numeric data in a numeric or decimal column and the database will make sure the data has only numbers.

If the data is in a nvarchar column, then the database won’t care if the data is a number or a date or strings/characters.

In this case you will need to check manually to see if the column only has numeric data.

IsNumeric is a function in SQL Server that you can use.

Here’s a script to show you how to use it.

create table products
(
item nvarchar(100),
price nvarchar(100)
)
go
insert into products
select 'product1','-100'
union
select 'product2','25.42'
union
select 'product3','test'
union
select 'product4','0.01'
go

select item,price,isnumeric(price) numeric_check
from products;

Isnumeric function results

select item,price,isnumeric(price) numeric_check
from products
where isnumeric(price) = 0;

 

 

Isnumeric function where filter

Note that there are a few problems with the IsNumeric Function:

http://www.sql-server-performance.com/forum/threads/problem-with-isnumeric.11181/

http://www.simple-talk.com/blogs/2011/01/13/isnumeric-broken-only-up-to-a-point/

 

Here are a few other posts you might enjoy:

How to Filter for SQL Null or Empty String | SQL Functions | SQL Where

by Leave a Comment

How to Remove Duplicate Rows in SQL Server

Video: How to Remove Duplicate Rows in SQL Server – SQL Training Online
In this video, I show you a trick on removing duplicate rows in SQL Server 2012.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How to Remove Duplicate Rows in SQL Server?

I first need a table that has duplicate records to remove, so here is a script to create some duplicates in an email address table.

create table email_list
(
name varchar(100)
,email varchar(300)
)
go
insert into email_list values ('Jan','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');
insert into email_list values ('Bob','[email protected]');
insert into email_list values ('Jill','[email protected]');

Remove Duplicate Rows in SQL Server

Now, the first method to remove the duplicates is in the following script that I adapted from Alexander’s Post on duplicates.

WITH list_rownumbers AS
(
SELECT name,email,
ROW_NUMBER() OVER (ORDER BY name,email) AS 'RowNumber'
FROM email_list
)
DELETE list_rownumbers WHERE RowNumber not in
(SELECT min(RowNumber) FROM list_rownumbers GROUP BY name,email)

 

If you just look at the SQL With statement (CTE), you will see that it simply numbers all of the name, email combinations using the Row_Number function. Then it uses the minimum rows number per name, email group as an exclusion to remove the duplicate rows.

The second script is and adaptation of Ritesh’s Post on duplicates.

WITH list_duplicates (name, email, duplicate_count) AS
(
SELECT name,email,
ROW_NUMBER() OVER(PARTITION BY name,email ORDER BY name,email) AS duplicate_count
FROM email_list
)
DELETE
FROM list_duplicates
WHERE duplicate_count > 1

 

If we focus on the CTE query, we see that is uses the Partition By statement to actually reset the row_number count on each name, email group.

It makes the delete statement a little easier to follow since all we have to do is delete where the row_number (duplicate_count) is larger than 1.

And that’s it.  That is how you Delete Duplicates in SQL.

 

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

 

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline