Happy holidays from T-SQL

Curious to know the result of the following query? All you need to do is, in SQL Server, goto SSMS, set the result mode to Text (Press CTRL+T) and then execute the following code

set nocount on select space(17-len(replicate(
char(135),no)))+ replicate(char(135),no*2-1)
from (select top 10 row_number() over (
order by (select 1)) as no from 
(select 0 as no union all select 0 union 
all select 0) as t1 
cross join (select 0 as no union 
all select 0 union all 
select 0) as t2) as t union all 
select space(14)+replicate(char(124),5) union 
all select space(10)+
cast(0x486170707920486F6C6964617973 
as varchar(100))
Advertisements

MySQL – Functions to get current date and time

You may often need to do some data filters based on the current date and time. In MySQL you can use the system functions available to get current date and time.

Refer the following examples.

1 Get current date

SELECT CURRENT_DATE() AS today_date

or

SELECT CURDATE() AS today_date

The result is

2017-11-20

2 Get current time

SELECT CURRENT_TIME() AS today_time

or

SELECT CURTIME() AS today_time

The result is

08:36:29

3 Get Current Date and time

SELECT NOW() AS today_date_time

The result is

2017-11-20T08:36:29Z

MySQL – How to create random number with fixed length?

One of my friends asked me “How do I create a 10 digit random number?”

MySQL supports RAND() system function which can produce a random number between 0 and 1 with 16 digits as the decimal part.

An example for a random value can be obtained using

SELECT RAND() as random_number;

The result is

random_number
-------------------
0.7638836063108254

Note that every time you run this code, you will get a different number.

Now to get 10 digit whole number, multiple it by 10000000000 and omit the decimal part by converting the result into UNSIGNED number

SELECT CAST(rand()*10000000000 as UNSIGNED) as random_number

The result is

random_number
-------------------
5176665713

This can be modified easily to get other number with fixed digits. For example if you want a random 4 digit number, you can use

SELECT CAST(rand()*10000 as UNSIGNED) as random_number

The result is

random_number
-------------------
5278

You can parameterize this to work for any number of fixed digits you want

MySQL – Simple ways to know version number with @@version and version()

If you want to know the version number of MySQL server, there are two simple methods available.

You can use system variable @@VERSION as shown below

SELECT @@VERSION as mysql_version;

Other method is to use system function VERSION()

SELECT VERSION() as mysql_version

Both of them returns the following result

mysql_version
-----------------
5.6.35

Depends on the version of the server, you may get the different version number

Note : @@VERSION is already available in SQL Server database engine as a global variable to get the version information

MySQL – Concatenate column values for each group with GROUP_CONCAT function

As I said in my earlier posts, MySQL has some interesting features that make the things simpler. GROUP_CONCAT is one of such functions

This function concatenates values from several rows into a single row separated by the delimiter specified.

For example consider the following example with simple data set

create table customer_visits(customer_id int, place_name varchar(100));

insert into customer_visits (customer_id,place_name)
select 1,'Chennai' union all
select 1,'Denmark' union all
select 1,'USA' union all
select 2,'UK' union all
select 2,'Argentina'

Now for each customer id, if you want to concatenate the visited places in a single string separated by comma, you can use the following code

select customer_id,
       GROUP_CONCAT(place_name) as visited_places
from
       customer_visits
group by
       customer_id

The result is

customer_id      visited_places
1                Chennai,Denmark,USA
2                UK,Argentina

Note that by default values are concatenated using comma. If you want to use different delimiter, say for example a semicolon (;), you can use SEPARATOR clause to do it as shown below.

select customer_id,
       GROUP_CONCAT(place_name SEPARATOR ';') as visited_places
from
       customer_visits
group by
      customer_id

The result is

customer_id      visited_places
1                Chennai;Denmark;USA
2                UK;Argentina

GROUP_CONCAT is really a quite handy function.

MySQL – UPSERT in a single statement

MySQL, an opensource RDBMS, has lot of interesting features. One of them is ON DUPLICATE KEY UPDATE statement. This statement adds a new row, if the unique or primary column is not available in the table, if available,UPDATE the row.

Consider the following dataset

create table test
(
       id int ,
       name varchar(100),
       status varchar(100)
);

CREATE unique INDEX id ON test (id);

Now add the following rows

insert into test(id,name,status)
values (1,'test1','') ;

insert into test(id,name,status)
values (2,'test2','') ;

If you select the rows

select * from test

It returns the following rows

id           name           status
1            test1
2            test2

When you try to add the following data

insert into test(id,name,status)
values (1,'test3','') ;

You get the following error

Duplicate entry ‘1’ for key ‘id’

Suppose if there exists a row for the id, you want update it, say status column with the value “duplicate” and not to add as new row. You can use ON DUPLICATE KEY UPDATE statement as shown below;

insert into test(id,name,status)
values (1,'test3','')
ON DUPLICATE KEY UPDATE status='duplicate';

If you select the rows

select * from test

It returns the following rows

id           name           status
1            test1         duplicate
2            test2

As you see new row is not added but status column of the row with id value 1 is updated with the value duplicate

Alias name should be preceded by “AS”

In my earlier post on Understanding Column alias I have shown different methods of using a column alias name

SELECT 100number
SELECT 100.number
SELECT 100'number'
SELECT 100"number"
SELECT 100[number]

As you see all works fine with number as the column alias name

But However you need to use AS before the alias name for better reading and standard
Now execute the following and see the result

drop table if exists #employee_master;
create table #employee_master
(
emp_id int, emp_name varchar(100), dob datetime
)
insert into #employee_master
select 1,'Sankar','19760910' union all
select 2,'Kamal','19680707' union all
select 3,'Rajesh','19661222'
Select emp_id, emp_name dob from #employee_master

The result is

emp_id      dob
----------- ------------
1           Sankar
2           Kamal
3           Rajesh

The SELECT statement lists out three columns but the output has only two columns. The data of emp_name are listed under the column name dob. It is because the missing comma between column names makes that dob becomes alias name for emp_name which is confusing and hard to detect if there are hundreds of lines in the procedure.

So in my opinion ,Alias name should be preceded by “AS” to avoid confusion and unexpected result.

What do you think of this?