SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

February 14, 2015 by Joey Blue Leave a Comment

How to use the SQL Server String Concat Function in SQL Server 2012

 

SQL Server 2012 includes a new Concat function that makes it easier to put strings, numbers, dates, and nulls together.

The old way was with ‘+’ signs and cast or convert.  Now you just use the SQL Server String Concat Function.

In this video I do a quick demonstration of the Concat Function.

Video: SQL Server String Concat


How to use the new SQL Server 2012 CONCAT Function to combine columns and strings.

I use the Chinook Sample Database and the following scripts:

select FirstName + ' ' + LastName + '-' + Company 
from customer

select CONCAT(FirstName,' ',LastName,'-',Company)
from customer

 

See the original  SQL Concat Function video.

Filed Under: SQL Tip Tagged With: SQL Concat, SQL Convert, SQL Server 2012, SQL String Concat

January 31, 2013 by Joey Blue Leave a Comment

How to Calculate a SQL Moving Average without a Cursor

Update:  If you are working with the newest versions of SQL Server, you can use the windowing functions to accomplish the same thing.  I posted the updated code at the end of the post.  For this video, I still like the thought process of anchoring to a date.

Video: 3 Day Moving Average in SQL


An efficient way to calculate a moving average in SQL using a few tricks to set date anchors.

There are debates on the best way to do a SQL Moving Average in SQL Server.

Some people think there are times when a cursor is most efficient.  Other’s think that you can do it all in a set-based way without the cursor.

The other day I was going to calculate a moving average and my first thought was to use a cursor.

I did some quick research and found this forum question: Moving Average in TSQL

There is a post that shows a subquery with an anchor date to help find the 1 and 2 day offset.

Here is the script you can use to test the 3 day SQL Moving Average final result.

CREATE TABLE [dbo].[daily_sales](
    [id] [int] NULL,
    [dt] [datetime] NULL,
    [revenue] [decimal](12, 2) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (1, CAST(0x0000A15700000000 AS DateTime), CAST(125.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (2, CAST(0x0000A15800000000 AS DateTime), CAST(114.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (3, CAST(0x0000A15900000000 AS DateTime), CAST(92.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (4, CAST(0x0000A15A00000000 AS DateTime), CAST(152.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (5, CAST(0x0000A15B00000000 AS DateTime), CAST(48.00 AS Decimal(12, 2)))
GO

Here is the final query.

SELECT        
            DATEADD(DAY, days_since_1900, '19000101') AS dt,
            AVG(revenue) AS Revenue_3_day_moving_average
FROM (
            select 
            DATEDIFF(DAY, '18991230', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL

            select 
            DATEDIFF(DAY, '18991231', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL
            select 
            DATEDIFF(DAY, '19000101', dt) AS days_since_1900
            ,revenue
            ,1 as Actualdate
            from daily_sales

) AS daily_sales_normalized_to_1900
GROUP BY    days_since_1900
HAVING        MAX(Actualdate) = 1
ORDER BY    dt

Here is the query you would use with SQL Server 2012.

select dt, 
    avg(revenue)
    OVER (
        ORDER BY dt
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )
FROM daily_sales

 

The original Youtube video: SQL Moving Average

Filed Under: SQL Training Tagged With: SQL Dateadd, SQL Datediff, SQL Datetime, SQL Group By, SQL Having, SQL Moving Average, SQL Server 2012, SQL Subquery

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

  • 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