SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

February 7, 2015 by Joey Blue 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 .

Filed Under: SQL Tip Tagged With: SQL, SQL Case Statement, SQL Cast, SQL Convert, SQL Datetime, SQL Isdate

January 24, 2013 by Joey Blue 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

Filed Under: SQL Tip, SQL Training Tagged With: Import, SQL, SQL Import, SQL Server 2012, SSMS

January 18, 2013 by Joey Blue 1 Comment

SQL Not Like with Multiple Values

I came across a forum post where someone wanted to use SQL NOT LIKE with multiple values.

 

They were trying to exclude multiple values from the SQL query, but they were needing to use wildcards.

If you wanted to just filter values without wildcards, you would use  the following query.

select *
from table1
where column1 not in ('value1','value2','value3');

 

The only problem was that they needed to compare using the LIKE operator.

It would be nice if you could just stick some wildcards in the in clause like this:

where column1 not in ('%value1%','%value2%','%value3%')

 

But, you can’t stick a wildcard inside of the IN clause. So, here is the easiest solution.

select *
from table1
where column1 not like '%value1%'
and column1 not like '%value2%'
and column1 not like'%value3%';

 

 

If you want to play around with the Boolean logic, you rearrange the query like this.

select *
from table1
where not (column1 like '%value1%'
or column1 like '%value2%'
or column1 like'%value3%');

SQL Not Like with Multiple Values

What happens if you don’t put those parenthesis in?

 

 

Here are a few other posts you might enjoy:

SQL Wildcards | SQL Multiple Filter | How to use the SQL In Statement with Subquery

 

 

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

 

 

Filed Under: SQL Training Tagged With: Beginning SQL, SQL, SQL IN, SQL Like, SQL Server 2012

January 10, 2013 by Joey Blue 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

Filed Under: SQL Training Tagged With: Decimal, Isnumeric, Numeric, SQL, SQL Server 2012

November 9, 2012 by Joey Blue 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

Filed Under: SQL Training Tagged With: SQL, SQL CTE, SQL Delete, SQL Duplicates, SQL Server 2012

  • 1
  • 2
  • Next Page »

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2025 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com