Micro Codes - "Coding Schools"

Sending Group Mail via SMTP in PHP

2011-07-07T02:01:00.001-07:00

This tutorial will you to send Email via SMTP using PHP coding. First we have to declare and assign the value for variables like First Name, Last Name, Email id to whom we need to send the email

index.php

$to="user@gmail.com";
$fn="Fisrt Name";
$ln="Last Name";
$name=$fn.' '.$ln;
$from="support@website.com";
$subject = "Welcome to Website";
$message = "Dear $name,
Your Welcome Message.
Thanks
www.website.com
";
include('smtpwork.php');

Now we will configure the SMTP, smtp.php

<?php
require("smtp.php");
require("sasl.php"); //SASL authentication
$from="support@yourwebsite.com";
$smtp=new smtp_class;
$smtp->host_name="www.website.com"; // Or IP address
$smtp->host_port=25;
$smtp->ssl=0;
$smtp->start_tls=0;
$smtp->localhost="localhost";
$smtp->direct_delivery=0;
$smtp->timeout=10;
$smtp->data_timeout=0;
$smtp->debug=1;
$smtp->html_debug=1;
$smtp->pop3_auth_host="";
$smtp->user="support@website.com"; // SMTP Username
$smtp->realm="";
$smtp->password="password"; // SMTP Password
$smtp->workstation="";
$smtp->authentication_mechanism="";
if($smtp->SendMessage(
$from,
array(
$to
),
array(
"From: $from",
"To: $to",
"Subject: $subject",
"Date: ".strftime("%a, %d %b %Y %H:%M:%S %Z")
),
"$message"))
{
echo "Message sent to $to OK.";
}
else
echo "Cound not seend the message to $to.\nError: ".$smtp->error;
?>

That’s all now we can send email to all

Download SMTP files using PHP

Image Cropping using JQuery & PHP Coding

2011-07-03T03:07:00.000-07:00

Today we are going to see how to crop the Image as we need using JQuery in PHP and we are going to store that cropped image in MySql database.

Here is the sample Screen Shot

Create the MySql database with needed information like user id, username, password, email, profile image, profile image small for cropped image.

CREATE TABLE `users` (
`uid` int(11) AUTO_INCREMENT PRIMARY KEY,
`username` varchar(255) UNIQUE KEY,
`password` varchar(100),
`email` varchar(255) UNIQUE KEY,
`profile_image` varchar(200),
`profile_image_small` varchar(200),
)

Now using PHP coding we are going to write the upload script to upload the image and by using HTML coding we are going to design the upload box and so on.

First we need include the database using include function and then we need to create the session for particular user and we need to specify the file format which it will accept and update the image in database.

index.php

<?php
include('db.php');
session_start();
$session_id=$_SESSION['user'];// Session ID
$path = "uploads/";
$valid_formats = array("jpg", "png", "gif", "bmp");
if(isset($_POST['submit']))
{
$name = $_FILES['photoimg']['name'];
$size = $_FILES['photoimg']['size'];
if(strlen($name))
{
list($txt, $ext) = explode(".", $name);
if(in_array($ext,$valid_formats) && $size<(250*1024))
{
$actual_image_name = time().substr($txt, 5).".".$ext;
$tmp = $_FILES['photoimg']['tmp_name'];
if(move_uploaded_file($tmp, $path.$actual_image_name))
{
mysql_query("UPDATE users SET profile_image='$actual_image_name' WHERE uid='$session_id'");
$image="<h1>Please drag on the image</h1><img src='uploads/".$actual_image_name."' id=\"photo\" ";
}
else
echo "failed";
}
else
echo "Invalid file formats..!";
}
else
echo "Please select image..!";
}
?>
//HTML Body
<?php echo $image; ?>
<div id="thumbs" ></div>
<div >
<form id="cropimage" method="post" enctype="multipart/form-data">
Upload your image <input type="file" name="photoimg" id="photoimg" />
<input type="hidden" name="image_name" id="image_name" value="<?php echo($actual_image_name)?>" />
<input type="submit" name="submit" value="Submit" />
</form>

Java Script Coding

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/
1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="scripts/jquery.imgareaselect.pack.js"></script>
<script type="text/javascript">
function getSizes(im,obj)
{
var x_axis = obj.x1;
var x2_axis = obj.x2;
var y_axis = obj.y1;
var y2_axis = obj.y2;
var thumb_width = obj.width;
var thumb_height = obj.height;
if(thumb_width > 0)
{
if(confirm("Do you want to save image..!"))
{
$.ajax({
type:"GET",
url:"ajax_image.php?t=ajax&img="+$("#image_name").val()+"&w="+
thumb_width+"&h="+thumb_height+"&x1="+x_axis+"&y1="+y_axis,
cache:false,
success:function(rsponse)
{
$("#cropimage").hide();
$("#thumbs").html("");
$("#thumbs").html("<img src='uploads/"+rsponse+"' />");
}
});
}
}
else
alert("Please select portion..!");
}
$(document).ready(function ()
{
$('img#photo').imgAreaSelect({
aspectRatio: '1:1',
onSelectEnd: getSizes
});
});
</script>

Ajax Coding (ajax_image.php)

<?php
include('db.php');
session_start();
$session_id=$_SESSION['user']; // Or Session ID
$t_width = 100; // Maximum thumbnail width
$t_height = 100; // Maximum thumbnail height
$new_name = "small".$session_id.".jpg"; // Thumbnail image name
$path = "uploads/";
if(isset($_GET['t']) and $_GET['t'] == "ajax")
{
extract($_GET);
$ratio = ($t_width/$w);
$nw = ceil($w * $ratio);
$nh = ceil($h * $ratio);
$nimg = imagecreatetruecolor($nw,$nh);
$im_src = imagecreatefromjpeg($path.$img);
imagecopyresampled($nimg,$im_src,0,0,$x1,$y1,$nw,$nh,$w,$h);
imagejpeg($nimg,$path.$new_name,90);
mysql_query("UPDATE users SET profile_image_small='$new_name' WHERE uid='$session_id'");
echo $new_name."?".time();
exit;
}
?>

Add database connectivity and other database option in separate file db.php

<?php
$mysql_hostname = "Host name";
$mysql_user = "UserName";
$mysql_password = "Password";
$mysql_database = "Database Name";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
mysql_select_db($mysql_database, $bd) or die("Could not select database");
?>

Generate text file using Java Script Coding

2011-07-01T10:48:00.000-07:00

In this tutorial we are going to see how to create file in web using Java Script. User have to enter the text in text and when they click the submit or download button, then the user can download that typed text in a text document.

First we will design the web page by using HTML, We need one Textbox and command button to download the text in textbox.

Sample Screenshots

HTML Coding

File Name:index.html

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8" />

        <title>Generating files with Java Script</title>

        
        <link rel="stylesheet" href="assets/css/styles.css" />

        
    </head>

    <body>

        <header>
            <h1>Generating Files with JavaScript</h1>
            </header>

        <form action="./" method="post">
            <textarea></textarea>
            <a href="#" class="blueButton" id="download">Download</a>
        </form>



        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.0/jquery.min.js"></script>
        <script src="assets/js/jquery.generateFile.js"></script>
        <script src="assets/js/script.js"></script>

    </body>
</html>

Now we need to check whether the textbox is empty and we need to name the file while download. Using PHP we can implement this,

PHP Coding

File Name: download.php

if(empty($_POST['filename']) || empty($_POST['content'])){
exit;
}

// Sanitizing the filename:
$filename = preg_replace('/[^a-z0-9\-\_\.]/i','',$_POST['filename']);

// Outputting headers:
header("Cache-Control: ");
header("Content-type: text/plain");
header('Content-Disposition: attachment; filename="'.$filename.'"');

echo $_POST['content'];

JQuery Coding

(function($){

	// Creating a jQuery plugin:

	$.generateFile = function(options){

		options = options || {};

		if(!options.script || !options.filename || !options.content){
			throw new Error("Please enter all the required config options!");
		}

		// Creating a 1 by 1 px invisible iframe:

		var iframe = $('<iframe>',{
			width:1,
			height:1,
			frameborder:0,
			css:{
				display:'none'
			}
		}).appendTo('body');

		var formHTML = '<form action="" method="post">'+
			'<input type="hidden" name="filename" />'+
			'<input type="hidden" name="content" />'+
			'</form>';

		// Giving IE a chance to build the DOM in
		// the iframe with a short timeout:

		setTimeout(function(){

			// The body element of the iframe document:

			var body = (iframe.prop('contentDocument') !== undefined) ?
							iframe.prop('contentDocument').body :
							iframe.prop('document').body;	// IE

			body = $(body);

			// Adding the form to the body:
			body.html(formHTML);

			var form = body.find('form');

			form.attr('action',options.script);
			form.find('input[name=filename]').val(options.filename);
			form.find('input[name=content]').val(options.content);

			// Submitting the form to download.php. This will
			// cause the file download dialog box to appear.

			form.submit();
		},50);
	};

})(jQuery);

assets/script.js

$(document).ready(function(){

    $('#download').click(function(e){

        $.generateFile({

            filename    : 'export.txt',

            content        : $('textarea').val(),

            script        : 'download.php'

        });

        e.preventDefault();

    });

    $('#downloadPage').click(function(e){

        $.generateFile({

            filename    : 'page.html',

            content        : $('html').html(),

            script        : 'download.php'

        });

        e.preventDefault();

    });

});

Download the source file

Attractive Menu using CSS3

2011-07-01T03:06:00.000-07:00

In this Tutorial we are going to see attractive menu using CSS3.

First design the menu using HTML tags,

<nav>
        <ul>
                <li><a href="#"><span>Home</span></a></li>
                <li><a href="#"><span>Categories</span></a></li>
                <li><a href="#"><span>About</span></a></li>
                <li><a href="#"><span>Portfolio</span></a></li>
                <li><a href="#"><span>Contact</span></a></li>
        </ul>
</nav>

Using CSS we will arrange the code for <li> and <ul>

nav ul
{
        padding: 0;
        margin: 0;
        list-style: none;
}

nav li
{
        float: left;
}

Now we have to style the anchor text.

nav a
{
    float: left;
    color: #eee;
    margin: 0 5px;
    padding: 3px;
    text-decoration: none;
    border: 1px solid #831608;
    font: bold 14px Arial, Helvetica;
    background-color: #831608;
    background-image: -moz-linear-gradient(#bb413b, #831608);
    background-image: -webkit-gradient(linear, left top, left bottom, from(#bb413b), to(#831608));
    background-image: -webkit-linear-gradient(#bb413b, #831608);
    background-image: -o-linear-gradient(#bb413b, #831608);
    background-image: -ms-linear-gradient(#bb413b, #831608);
    background-image: linear-gradient(#bb413b, #831608);
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    border-radius: 5px;
    text-shadow: 0 -1px 0 rgba(0,0,0,.8);
    -moz-box-shadow: 0 1px 0 rgba(255, 255, 255, 0.3), 0 3px 0 rgba(0, 0, 0, 0.7), 0 2px 2px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.5) inset;
    -webkit-box-shadow: 0 1px 0 rgba(255, 255, 255, 0.3), 0 3px 0 rgba(0, 0, 0, 0.7), 0 2px 2px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.5) inset;
    box-shadow: 0 1px 0 rgba(255, 255, 255, 0.3), 0 3px 0 rgba(0, 0, 0, 0.7), 0 2px 2px rgba(0, 0, 0, 0.5), 0 1px 0 rgba(255, 255, 255, 0.5) inset;
}

nav a:hover
{
    background-color: #bb413b;
    background-image: -moz-linear-gradient(#831608, #bb413b);
    background-image: -webkit-gradient(linear, left top, left bottom, from(#831608), to(#bb413b));
    background-image: -webkit-linear-gradient(#831608, #bb413b);
    background-image: -o-linear-gradient(#831608, #bb413b);
    background-image: -ms-linear-gradient(#831608, #bb413b);
    background-image: linear-gradient(#831608, #bb413b);
}

nav a:active
{
    background: #bb413b;
    position: relative;
    top: 2px;
    -moz-box-shadow: 0 0 3px rgba(0, 0, 0, 0.7) inset;
    -webkit-box-shadow: 0 0 3px rgba(0, 0, 0, 0.7) inset;
    box-shadow: 0 0 3px rgba(0, 0, 0, 0.7) inset;
}

Now style the Span Element

nav span
{
    border: 1px dashed #eba1a3;
    display: inline-block;
    padding: 4px 15px;
    cursor: pointer;
    background-color: #bb413b;
    background-image: -moz-linear-gradient(#d4463c, #aa2618);
    background-image: -webkit-gradient(linear, left top, left bottom, from(#d4463c), to(#aa2618));
    background-image: -webkit-linear-gradient(#d4463c, #aa2618);
    background-image: -o-linear-gradient(#d4463c, #aa2618);
    background-image: -ms-linear-gradient(#d4463c, #aa2618);
    background-image: linear-gradient(#d4463c, #aa2618);
}

nav a:hover span
{
    background-color: #bb413b;
    background-image: -moz-linear-gradient(#aa2618, #d4463c);
    background-image: -webkit-gradient(linear, left top, left bottom, from(#aa2618), to(#d4463c));
    background-image: -webkit-linear-gradient(#aa2618, #d4463c);
    background-image: -o-linear-gradient(#aa2618, #d4463c);
    background-image: -ms-linear-gradient(#aa2618, #d4463c);
    background-image: linear-gradient(#aa2618, #d4463c);
}

Style the font property,

font: bold 14px Arial, Helvetica;

Download CSS3 Menu

Ajax Coding: Top Drop Down Menu

2011-07-01T02:51:00.000-07:00

In this Ajax Coding we are going to design a menu which can slide downwards and we can put some content in that dropdown area.

First we need to add the script in the head menu,

<script type='text/javascript' src='js/prototype.js'></script>
<script type='text/javascript' src='/js/scriptaculous.js?load=effects'></script>
<script type='text/javascript' src='js/e24tabmenu.js'></script>

After Adding Script, now we need to design the menu using HTML tags,

<div id="menu" ><!---menu container-->
<div id="item_3d" class="menutarget">
	Contenido</div>
<div id="item_gall" class="menutarget">
	Contenido</div>
<div id="item_menu" class="menutarget">
	Contenido</div>
<div id="item_efec" class="menutarget">
	Contenido</div>
<!---tabs del menu-->
  <a id="3d" href="#3d" rel="e24menuitem[item_3d]"><img src="img/3d.gif" alt="Efectos 3D" /></a>
  <a id="gall" href="#galerias" rel="e24menuitem[item_gall]"><img src="img/galerias.gif" alt="Galeria de fotos AJAX" /></a>
  <a id="menus" href="#menus" rel="e24menuitem[item_menu]" ><img src="img/menus.gif" alt="Efectos de Menus" /></a>
  <a id="efec" href="#efectos" rel="e24menuitem[item_efec]"><img src="img/efectos.gif" alt="Otros efectos ajax" /></a>
  <!---tabs del menu-->
<div id="maincontent">
	Contenido principal</div>
<!--texto--></div>
<!--menu container-->

Now we have to add the java script in menu,

oe24TabMenu = new e24TabMenu( 'menu', { duration: 1.0, transition: Effect.Transitions.sinoidal } );

Download the Top Menu sliding using Ajax Coding

Sliding Read More in JQuery

2011-06-28T21:34:00.000-07:00

In this tutorial we are going to see how read more option can be effectively and attractively used using JQuery.

XHTML Coding

Here we are creating the layout of the page using <div> tag, then using that <div> tag we are going to apply the JQuery.

<div id="container">
    <h1>jQuery slide with minimum height
    <h2>About Billabong</h2>
    <div id="wrap">
        <div>
            <p>Gordon Merchant founded Billabong in Burleigh Heads on the Gold Coast in 1973. Combining his passion for surfing with hard work, Gordon designed boardshorts, manufacturing them on the kitchen table and selling through local surf shops and markets.</p>
            <p>Gordon developed his own stitching technique, which made the garments more durable, cost effective and less labor intensive. He employed machinists, moved the operation into a factory, set up a distribution network and sponsored a team of renowned Australian surfers. The business thrived.</p>
            <p>Since those beginnings, Billabong has expanded its product range to include boardsport products such as wetsuits, watches, surfboards, snowboard outerwear and skateboarding apparel.</p>
            <p>Information courtesy of <a title="Billabong" href="http://www.billabong.com/us/">Billabong</a>.</p>
        </div>
        <div id="gradient"></div>
    </div>
    <div id="read-more"></div>
</div>

JQuery Coding

Using JQuery Coding we are going to apply the sliding animation.

$(function(){
    var slideHeight = 75; // px
    var defHeight = $('#wrap').height();
    if(defHeight >= slideHeight){
        $('#wrap').css('height' , slideHeight + 'px');
        $('#read-more').append('<a href="#">Click to Read More</a>');
        $('#read-more a').click(function(){
            var curHeight = $('#wrap').height();
            if(curHeight == slideHeight){
                $('#wrap').animate({
                  height: defHeight
                }, "normal");
                $('#read-more a').html('Close');
                $('#gradient').fadeOut();
            }else{
                $('#wrap').animate({
                  height: slideHeight
                }, "normal");
                $('#read-more a').html('Click to Read More');
                $('#gradient').fadeIn();
            }
            return false;
        });
    }
});

CSS Coding

/* simple reset */
html,body,div,h2,p {margin:0;padding:0;}

html {
font:1em Arial, Helvetica, sans-serif;
color:#444;
}
a {color:#0087f1;}
p {margin-bottom:5px;}
#container {
margin:0 auto;
width:600px;
}
#container h2 {
font-size:20px;
color:#0087f1;
}
#wrap {
position: relative;
padding: 10px;
overflow: hidden;
}
#gradient {
width:100%;
height:35px;
background:url(images/bg-gradient.png) repeat-x;
position:absolute;
bottom:0;
left:0;
}
#read-more {
padding:5px;
border-top:4px double #ddd;
background:#fff;
color:#333;
}
#read-more a {
padding-right:22px;
background:url(images/icon-arrow.gif) no-repeat 100% 50%;
font-weight:bold;
text-decoration:none;
}
#read-more a:hover {color:#000;}

Source

Multi-color for Tool Tips–”CSS3”

2011-06-27T11:15:00.001-07:00

In this tutorial we are going learn how to make multi color tool tips for the text.

Download the Multi Color for Tool Tips Source Code

HTML

<a href="#" class="tooltip">
  your text
  <span>Your tooltip description</span>
</a>

Java Script

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script type="text/javascript">
  $(function() {
    if ($.browser.msie && $.browser.version.substr(0,1)<7)
    {
      $('.tooltip').mouseover(function(){
            $(this).children('span').show();
          }).mouseout(function(){
            $(this).children('span').hide();
          })
    }
  });
</script>

CSS

.tooltip
{
  position: relative;
  background: #eaeaea;
  cursor: help;
  display: inline-block;
  text-decoration: none;
  color: #222;
  outline: none;
}

.tooltip span
{
  visibility: hidden;
  position: absolute;
  bottom: 30px;
  left: 50%;
  z-index: 999;
  width: 230px;
  margin-left: -127px;
  padding: 10px;
  border: 2px solid #ccc;
  opacity: .9;
  background-color: #ddd;
  background-image: -webkit-linear-gradient(rgba(255,255,255,.5), rgba(255,255,255,0));
  background-image: -moz-linear-gradient(rgba(255,255,255,.5), rgba(255,255,255,0));
  background-image: -ms-linear-gradient(rgba(255,255,255,.5), rgba(255,255,255,0));
  background-image: -o-linear-gradient(rgba(255,255,255,.5), rgba(255,255,255,0));
  background-image: linear-gradient(rgba(255,255,255,.5), rgba(255,255,255,0));
  -moz-border-radius: 4px;
  border-radius: 4px;
  -moz-box-shadow: 0 1px 2px rgba(0,0,0,.4), 0 1px 0 rgba(255,255,255,.5) inset;
  -webkit-box-shadow: 0 1px 2px rgba(0,0,0,.4), 0 1px 0 rgba(255,255,255,.5) inset;
  box-shadow: 0 1px 2px rgba(0,0,0,.4), 0 1px 0 rgba(255,255,255,.5) inset;
  text-shadow: 0 1px 0 rgba(255,255,255,.4);
}

.tooltip:hover
{
  border: 0; /* IE6 fix */
}

.tooltip:hover span
{
  visibility: visible;
}

.tooltip span:before,
.tooltip span:after
{
  content: "";
  position: absolute;
  z-index: 1000;
  bottom: -7px;
  left: 50%;
  margin-left: -8px;
  border-top: 8px solid #ddd;
  border-left: 8px solid transparent;
  border-right: 8px solid transparent;
  border-bottom: 0;
}

.tooltip span:before
{
  border-top-color: #ccc;
  bottom: -8px;
}

Download the Multi Color for Tool Tips Source Code

Finding the Min/Max Value in a Column : SQL

2008-06-30T00:44:00.000-07:00

Problem

You want to find the highest and lowest values in a given column. For example, you want to find the highest and lowest salaries for all employees, as well as the highest and lowest salaries for each department.

Solution

When searching for the lowest and highest salaries for all employees, simply use the functions MIN and MAX, respectively:

 
 1 select min(sal) as min_sal, max(sal) as max_sal
 2   from emp

    MIN_SAL     MAX_SAL
 ----------  ----------
        800        5000

When searching for the lowest and highest salaries for each department, use the functions MIN and MAX with the GROUP BY clause:

 1 select deptno, min(sal) as min_sal, max(sal) as max_sal
 2   from emp
 3  group by deptno

     DEPTNO     MIN_SAL     MAX_SAL
 ----------  ----------  ----------
         10        1300        5000
         20         800        3000
         30         950        2850

Discussion

When searching for the highest or lowest values, and in cases where the whole table is the group or window, simply apply the MIN or MAX function to the column you are interested in without using the GROUP BY clause.

Remember that the MIN and MAX functions ignore NULLs, and that you can have NULL groups as well as NULL values for columns in a group. The following are examples that ultimately lead to a query using GROUP BY that returns NULL values for two groups (DEPTNO 10 and 20):

 
 select deptno, comm
   from emp
  where deptno in (10,30)
  order by 1


      DEPTNO       COMM
  ---------- ----------
          10
          10
          10
          30        300
          30        500
          30
          30          0
          30       1300
          30

 
 select min(comm), max(comm)
   from emp

  MIN(COMM)   MAX(COMM)
 ----------  ----------
          0        1300

 
 select deptno, min(comm), max(comm)
   from emp
  group by deptno

      DEPTNO  MIN(COMM)   MAX(COMM)
  ---------- ----------  ----------
          10
          20
          30          0        1300

Remember, Even if nothing other than aggregate functions are listed in the SELECT clause, you can still group by other columns in the table; for example:

 select min(comm), max(comm)
   from emp
  group by deptno

  MIN(COMM)   MAX(COMM)
 ----------  ----------
          0        1300

Here you are still grouping by DEPTNO even though it is not in the SELECT clause. Including the column you are grouping by in the SELECT clause often improves readability, but is not mandatory. It is mandatory, however, that any column in the SELECT list of a GROUP BY query also be listed in the GROUP BY clause.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Computing an Average : SQL Tutorial

2008-06-25T01:59:00.000-07:00

Problem

You want to compute the average value in a column, either for all rows in a table or for some subset of rows. For example, you might want to find the average salary for all employees as well as the average salary for each department.

Solution

When computing the average of all employee salaries, simply apply the AVG function to the column containing those salaries. By excluding a WHERE clause, the average is computed against all non-NULL values:

 
 1 select avg(sal) as avg_sal
 2   from emp

    AVG_SAL
 ----------
 2073.21429

To compute the average salary for each department, use the GROUP BY clause to create a group corresponding to each department:

 
 1 select deptno, avg(sal) as avg_sal
 2   from emp
 3  group by deptno

     DEPTNO     AVG_SAL
 ----------  ----------
         10  2916.66667
         20        2175
         30  1566.66667

Discussion

When finding an average where the whole table is the group or window, simply apply the AVG function to the column you are interested in without using the GROUP BY clause. It is important to realize that the function AVG ignores NULLs. The effect of NULL values being ignored can be seen here:

 create table t2(sal integer)
 insert into t2 values (10)
 insert into t2 values (20)
 insert into t2 values (null)

 
 select avg(sal)    select distinct 30/2
   from t2            from t2

   AVG(SAL)               30/2
 ----------         ----------
         15                 15

 
 select avg(coalesce(sal,0))    select distinct 30/3
   from t2                        from t2
   
 AVG(COALESCE(SAL,0))                 30/3
 --------------------           ----------
                   10                   10

The COALESCE function will return the first non-NULL value found in the list of values that you pass. When NULL SAL values are converted to zero, the average changes. When invoking aggregate functions, always give thought to how you want NULLs handled.

The second part of the solution uses GROUP BY (line 3) to divide employee records into groups based on department affiliation. GROUP BY automatically causes aggregate functions such as AVG to execute and return a result for each group. In this example, AVG would execute once for each department-based group of employee records.

It is not necessary, by the way, to include GROUP BY columns in your select list. For example:

 
 select avg(sal)
   from emp
  group by deptno

   AVG(SAL)
 ----------
 2916.66667
       2175
 1566.66667

You are still grouping by DEPTNO even though it is not in the SELECT clause. Including the column you are grouping by in the SELECT clause often improves readability, but is not mandatory. It is mandatory, however, to avoid placing columns in your SELECT list that are not also in your GROUP BY clause.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Parsing an IP Address in SQL Server

2008-06-23T22:20:00.001-07:00

Problem

You want to parse an IP address's fields into columns. Consider the following IP address:

 111.22.3.4

You would like the result of your query to be:

 A     B    C      D
 ----- ----- ----- ---
 111   22    3     4

Solution

The solution depends on the built-in functions provided by your DBMS. Regardless of your DBMS, being able to locate periods and the numbers immediately surrounding them are the keys to the solution.

DB2

Use the recursive WITH clause to simulate an iteration through the IP address while using SUBSTR to easily parse it. A leading period is added to the IP address so that every set of numbers has a period in front of it and can be treated the same way.

  1 with x (pos,ip) as (
  2   values (1,'.92.111.0.222')
  3   union all
  4  select pos+1,ip from x where pos+1 <= 20
  5 )
  6 select max(case when rn=1 then e end) a,
  7        max(case when rn=2 then e end) b,
  8        max(case when rn=3 then e end) c,
  9        max(case when rn=4 then e end) d
 10   from (
 11 select pos,c,d,
 12        case when posstr(d,'.') > 0 then substr(d,1,posstr(d,'.')-1)
 13             else d
 14        end as e,
 15        row_number() over( order by pos desc) rn
 16   from (
 17 select pos, ip,right(ip,pos) as c, substr(right(ip,pos),2) as d
 18   from x
 19  where pos <= length(ip)
 20   and substr(right(ip,pos),1,1) = '.'
 21      ) x
 22      ) y

MySQL

The function SUBSTR_INDEX makes parsing an IP address an easy operation:

 1 select substring_index(substring_index(y.ip,'.',1),'.',-1) a,
 2        substring_index(substring_index(y.ip,'.',2),'.',-1) b,
 3        substring_index(substring_index(y.ip,'.',3),'.',-1) c,
 4        substring_index(substring_index(y.ip,'.',4),'.',-1) d
 5   from (select '92.111.0.2' as ip from t1) y

Oracle

Use the built-in function SUBSTR and INSTR to parse and navigate through the IP address:

 1 select ip,
 2        substr(ip, 1, instr(ip,'.')-1 ) a,
 3        substr(ip, instr(ip,'.')+1,
 4                    instr(ip,'.',1,2)-instr(ip,'.')-1 ) b,
 5        substr(ip, instr(ip,'.',1,2)+1,
 6                    instr(ip,'.',1,3)-instr(ip,'.',1,2)-1 ) c,
 7        substr(ip, instr(ip,'.',1,3)+1 ) d
 8   from (select '92.111.0.2' as ip from t1)

PostgreSQL

Use the built-in function SPLIT_PART to parse an IP address:

 1 select split_part(y.ip,'.',1) as a,
 2        split_part(y.ip,'.',2) as b,
 3        split_part(y.ip,'.',3) as c,
 4        split_part(y.ip,'.',4) as d
 5   from (select cast('92.111.0.2' as text) as ip from t1) as y

SQL Server

  1  with x (pos,ip) as (
  2    select 1 as pos,'.92.111.0.222' as ip from t1
  3    union all
  4   select pos+1,ip from x where pos+1 <= 20
  5  )
  6  select max(case when rn=1 then e end) a,
  7         max(case when rn=2 then e end) b,
  8         max(case when rn=3 then e end) c,
  9         max(case when rn=4 then e end) d
 10    from  (
 11  select pos,c,d,
 12         case when charindex('.',d) > 0
 13            then substring(d,1,charindex('.',d)-1)
 14            else d
 15        end as e,
 16        row_number() over(order by pos desc) rn
 17   from (
 18 select pos, ip,right(ip,pos) as c,
 19        substring(right(ip,pos),2,len(ip)) as d
 20   from x
 21  where pos <= len(ip)
 22    and substring(right(ip,pos),1,1) = '.'
 23       ) x
 24       ) y

Discussion

By using the built-in functions for your database, you can easily walk through parts of a string. The key is being able to locate each of the periods in the address. Then you can parse the numbers between each.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Extracting the nth Delimited Substring : SQL

2008-06-22T23:10:00.000-07:00

Problem

You want to extract a specified, delimited substring from a string. Consider the following view V, which generates source data for this problem:

 create view V as
 select 'mo,larry,curly' as name
   from t1
  union all
 select 'tina,gina,jaunita,regina,leena' as name
   from t1

Output from the view is as follows:

 
 select * from v

 NAME
 -------------------
 mo,larry,curly
 tina,gina,jaunita,regina,leena

You would like to extract the second name in each row, so the final result set would be:

   SUB
 -----
 larry
 gina

Solution

The key to solving this problem is to return each name as an individual row while preserving the order in which the name exists in the list. Exactly how you do these things depends on which DBMS you are using.

DB2

After walking the NAMEs returned by view V, use the function ROW_NUMBER to keep only the second name from each string:

  1 select substr(c,2,locate(',',c,2)-2)
  2   from (
  3 select pos, name, substr(name, pos) c,
  4        row_number() over( partition by name
  5                       order by length(substr(name,pos)) desc) rn
  6  from (
  7 select ',' ||csv.name|| ',' as name,
  8         cast(iter.pos as integer) as pos
  9   from V csv,
 10        (select row_number( ) over( ) pos from t100 ) iter
 11  where iter.pos <= length(csv.name)+2
 12        ) x
 13  where length(substr(name,pos)) > 1
 14   and substr(substr(name,pos),1,1) = ','
 15        ) y
 16  where rn = 2

MySQL

After walking the NAMEs returned by view V, use the position of the commas to return only the second name in each string:

  1 select name
  2   from (
  3 select iter.pos,
  4        substring_index(
  5        substring_index(src.name,',',iter.pos),',',-1) name
  6   from V src,
  7        (select id pos from t10) iter,
  8  where iter.pos <=
  9        length(src.name)-length(replace(src.name,',',''))
 10        ) x
 11  where pos = 2

Oracle

After walking the NAMEs returned by view V, retrieve the second name in each list by using SUBSTR and INSTR:

  1 select sub
  2   from (
  3 select iter.pos,
  4        src.name,
  5        substr( src.name,
  6         instr( src.name,',',1,iter.pos )+1,
  7         instr( src.name,',',1,iter.pos+1 ) -
  8         instr( src.name,',',1,iter.pos )-1) sub
  9   from (select ','||name||',' as name from V) src,
 10        (select rownum pos from emp) iter
 11  where iter.pos <>
 12        )
 13  where pos = 2

PostgreSQL

Use the function SPLIT_PART to help return each individual name as a row:

  1 select name
  2   from (
  3 select iter.pos, split_part(src.name,',',iter.pos) as name
  4   from (select id as pos from t10) iter,
  5        (select cast(name as text) as name from v) src
  7  where iter.pos <=
  8         length(src.name)-length(replace(src.name,',',''))+1
  9        ) x
 10  where pos = 2

SQL Server

After walking the NAMEs returned by view V, use the function ROW_NUMBER to keep only the second name from each string:

  1 select substring(c,2,charindex(',',c,2)-2)
  2   from (
  3 select pos, name, substring(name, pos, len(name)) as c,
  4        row_number() over(
  5         partition by name
  6         order by len(substring(name,pos,len(name))) desc) rn
  7   from (
  8 select ',' + csv.name + ',' as name,
  9        iter.pos
 10   from V csv,
 11        (select id as pos from t100 ) iter
 12  where iter.pos <= len(csv.name)+2
 13        ) x
 14  where len(substring(name,pos,len(name))) > 1
 15    and substring(substring(name,pos,len(name)),1,1) = ','
 16        ) y
 17  where rn = 2

Discussion

DB2 and SQL Server

The syntax is slightly different between these two DBMSs, but the technique is the same. I will use the solution for DB2 for the discussion. The strings are walked and the results are represented by inline view X:

 
 select ','||csv.name|| ',' as name,
         iter.pos
   from v csv,
         (select row_number() over( ) pos from t100 ) iter
  where iter.pos <= length(csv.name)+2

 EMPS                             POS
 ------------------------------- ----
 ,tina,gina,jaunita,regina,leena,    1
 ,tina,gina,jaunita,regina,leena,    2
 ,tina,gina,jaunita,regina,leena,    3
 …

The next step is to then step through each character in each string:

 
 select pos, name, substr(name, pos) c,
         row_number() over(partition by name
                         order by length(substr(name, pos)) desc) rn
   from (
 select ','||csv.name||',' as name,
        cast(iter.pos as integer) as pos
   from v csv,
       (select row_number() over() pos from t100 ) iter
  where iter.pos <= length(csv.name)+2
        ) x
  where length(substr(name,pos)) > 1

 POS EMPS            C                RN
 --- --------------- ---------------- --
   1 ,mo,larry,curly, ,mo,larry,curly, 1
   2 ,mo,larry,curly, mo,larry,curly,  2
   3 ,mo,larry,curly, o,larry,curly,   3
   4 ,mo,larry,curly, ,larry,curly,    4
   …

Now that different portions of the string are available to you, simply identify which rows to keep. The rows you are interested in are the ones that begin with a comma; the rest can be discarded:

 
 select pos, name, substr(name,pos) c,
        row_number() over(partition by name
                        order by length(substr(name, pos)) desc) rn
   from (
 select ','||csv.name||',' as name,
        cast(iter.pos as integer) as pos
   from v csv,
        (select row_number() over( ) pos from t100 ) iter 
  where iter.pos <= length(csv.name)+2
        ) x 
  where length(substr(name,pos)) > 1 
     and substr(substr(name,pos),1,1) = ','

 POS  EMPS                               C                                    RN
  ---  --------------                     ----------------                      --
    1  ,mo,larry,curly,                   ,mo,larry,curly,                       1
    4  ,mo,larry,curly,                   ,larry,curly,                          2
   10  ,mo,larry,curly,                   ,curly,                                3
    1  ,tina,gina,jaunita,regina,leena,   ,tina,gina,jaunita,regina,leena,       1
    6  ,tina,gina,jaunita,regina,leena,   ,gina,jaunita,regina,leena,            2
   11  ,tina,gina,jaunita,regina,leena,   ,jaunita,regina,leena,                 3
   19  ,tina,gina,jaunita,regina,leena,   ,regina,leena,                         4
   26  ,tina,gina,jaunita,regina,leena,   ,leena,                                5

This is an important step as it sets up how you will get the nth substring. Notice that many rows have been eliminated from this query because of the following condition in the WHERE clause:

 substr(substr(name,pos),1,1) = ','

You'll notice that ,larry,curly, was ranked 4, but now is ranked 2. Remember, the WHERE clause is evaluated before the SELECT, so the rows with leading commas are kept, then ROW_NUMBER performs its ranking. At this point it's easy to see that, to get the nth substring you want rows where RN equals n. The last step is to keep only the rows you are interested in (in this case where RN equals 2) and use SUBSTR to extract the name from that row. The name to keep is the first name in the row: larry from ,larry,curly, and gina from ,gina,jaunita,regina,leena,.

MySQL

The inline view X walks each string. You can determine how many values are in each string by counting the delimiters in the string:

 
 select iter.pos, src.name
   from (select id pos from t10) iter,
        V src
  where iter.pos <=
        length(src.name)-length(replace(src.name,',',''))

 +------+--------------------------------+
 | pos  | name                           |
 +------+--------------------------------+
 |    1 | mo,larry,curly                 |
 |    2 | mo,larry,curly                 |
 |    1 | tina,gina,jaunita,regina,leena |
 |    2 | tina,gina,jaunita,regina,leena |
 |    3 | tina,gina,jaunita,regina,leena |
 |    4 | tina,gina,jaunita,regina,leena |
 +------+--------------------------------+

In this case, there is one fewer row than values in each string because that's all that is needed. The function SUBSTRING_INDEX takes care of parsing the needed values:

 
 select iter.pos,src.name name1,
         substring_index(src.name,',',iter.pos) name2,
         substring_index(
          substring_index(src.name,',',iter.pos),',',-1) name3
    from (select id pos from t10) iter,
         V src
   where  iter.pos <=
         length(src.name)-length(replace(src.name,',',''))

+------+--------------------------------+--------------------------+---------+ 
| pos  | name1                        | name2                      | name3   | 
+------+--------------------------------+--------------------------+---------+
|    1 | mo,larry,curly                 | mo                       | mo      |
|    2 | mo,larry,curly                 | mo,larry                 | larry   |
|    1 | tina,gina,jaunita,regina,leena | tina                     | tina    |
|    2 | tina,gina,jaunita,regina,leena | tina,gina                | gina    |
|    3 | tina,gina,jaunita,regina,leena | tina,gina,jaunita        | jaunita |
|    4 | tina,gina,jaunita,regina,leena | tina,gina,jaunita,regina | regina  |
+------+--------------------------------+--------------------------+---------+

I've shown three name fields, so you can see how the nested SUBSTRING_INDEX calls work. The inner call returns all characters to the left of the nth occurrence of a comma. The outer call returns everything to the right of the first comma it finds (starting from the end of the string). The final step is to keep the value for NAME3 where POS equals n, in this case 2.

Oracle

The inline view walks each string. The number of times each string is returned is determined by how many values are in each string. The solution finds the number of values in each string by counting the number of delimiters in it. Because each string is enclosed in commas, the number of values in a string is the number of commas minus one. The strings are then UNIONed and joined to a table with a cardinality that is at least the number of values in the largest string. The functions SUBSTR and INSTR use the value of POS to parse each string:

 
 select iter.pos, src.name,
        substr( src.name,
         instr( src.name,',',1,iter.pos )+1,
         instr( src.name,',',1,iter.pos+1 )
         instr( src.name,',',1,iter.pos )-1) sub
   from (select ','||name||',' as name from v) src,
        (select rownum pos from emp) iter
  where iter.pos <>

 POS NAME                              SUB
 --- --------------------------------- -------------
   1 ,mo,larry,curly,                  mo
   1 , tina,gina,jaunita,regina,leena, tina
   2 ,mo,larry,curly,                  larry
   2 , tina,gina,jaunita,regina,leena, gina
   3 ,mo,larry,curly,                  curly
   3 , tina,gina,jaunita,regina,leena, jaunita
   4 , tina,gina,jaunita,regina,leena, regina 
   5 , tina,gina,jaunita,regina,leena, leena

The first call to INSTR within SUBSTR determines the start position of the substring to extract. The next call to INSTR within SUBSTR finds the position of the nth comma (same as the start position) as well the position of the nth + 1 comma. Subtracting the two values returns the length of the substring to extract. Because every value is parsed into its own row, simply specify WHERE POS = n to keep the nth substring (in this case, where POS = 2, so, the second substring in the list).

PostgreSQL

The inline view X walks each string. The number of rows returned is determined by how many values are in each string. To find the number of values in each string, find the number of delimiters in each string and add one. The function SPLIT_PART uses the values in POS to find the nth occurrence of the delimiter and parse the string into values:

 
 select iter.pos, src.name as name1,
         split_part(src.name,',',iter.pos) as name2
    from (select id as pos from t10) iter,
         (select cast(name as text) as name from v) src
   where iter.pos <=
         length(src.name)-length(replace(src.name,',',''))+1

  pos |             name1              |  name2
 -----+--------------------------------+---------
    1 | mo,larry,curly                 | mo
    2 | mo,larry,curly                 | larry
    3 | mo,larry,curly                 | curly
    1 | tina,gina,jaunita,regina,leena | tina
    2 | tina,gina,jaunita,regina,leena | gina
    3 | tina,gina,jaunita,regina,leena | jaunita
    4 | tina,gina,jaunita,regina,leena | regina
    5 | tina,gina,jaunita,regina,leena | leena

I've shown NAME twice so you can see how SPLIT_PART parses each string using POS. Once each string is parsed, the final step is the keep the rows where POS equals the nth substring you are interested in, in this case, 2.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Identifying Strings That Can Be Treated as Numbers : SQL

2008-06-22T00:51:00.000-07:00

Problem

You have a column that is defined to hold character data. Unfortunately, the rows contain mixed numeric and character data. Consider view V:

 create view V as
 select replace(mixed,' ','') as mixed
   from (
 select substr(ename,1,2)||
        cast(deptno as char(4))||
        substr(ename,3,2) as mixed
   from emp
  where deptno = 10
  union all
 select cast(empno as char(4)) as mixed
   from emp
  where deptno = 20
  union all
 select ename as mixed
   from emp
  where deptno = 30
        ) x
 select * from v

  MIXED
  --------------
  CL10AR
  KI10NG
  MI10LL
  7369
  7566
  7788
  7876
  7902
  ALLEN
  WARD
  MARTIN
  BLAKE
  TURNER
  JAMES

You want to return rows that are numbers only, or that contain at least one number. If the numbers are mixed with character data, you want to remove the characters and return only the numbers. For the sample data above you want the following result set:

Solution

The functions REPLACE and TRANSLATE are extremely useful for manipulating strings and individual characters. The key is to convert all numbers to a single character, which then makes it easy to isolate and identify any number by referring to a single character.

DB2

Use functions TRANSLATE, REPLACE, and POSSTR to isolate the numeric characters in each row. The calls to CAST are necessary in view V; otherwise, the view will fail to be created due to type conversion errors. You'll need the function REPLACE to remove extraneous white space due to casting to the fixed length CHAR:

  1 select mixed old,
  2        cast(
  3          case
  4          when
  5            replace(
  6           translate(mixed,'9999999999','0123456789'),'9','') = ''
  7          then
  8             mixed
  9          else replace(
 10            translate(mixed,
 11               repeat('#',length(mixed)),
 12             replace(
 13              translate(mixed,'9999999999','0123456789'),'9','')),
 14                      '#','')
 15          end as integer ) mixed
 16   from V
 17  where posstr(translate(mixed,'9999999999','0123456789'),'9') > 0

MySQL

The syntax for MySQL is slightly different and will define view V as:

 create view V as
 select concat(
          substr(ename,1,2),
          replace(cast(deptno as char(4)),' ',''),
          substr(ename,3,2)
        ) as mixed
   from emp
  where deptno = 10
  union all
 select replace(cast(empno as char(4)), ' ', '')
   from emp where deptno = 20
  union all
 select ename from emp where deptno = 30

Because MySQL does not support the TRANSLATE function, you must walk each row and evaluate it on a character-by-character basis.

  1 select cast(group_concat(c order by pos separator '') as unsigned)
  2        as MIXED1
  3   from (
  4 select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c
  5   from V,
  6        ( select id pos from t10 ) iter
  7  where iter.pos <= length(v.mixed)
  8    and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57
  9        ) y
 10  group by mixed
 11  order by 1

Oracle

Use functions TRANSLATE, REPLACE, and INSTR to isolate the numeric characters in each row. The calls to CAST are not necessary in view V. Use the function REPLACE to remove extraneous white space due to casting to the fixed length CHAR. If you decide you would like to keep the explicit type conversion calls in the view definition, it is suggested you cast to VARCHAR2:

  1 select to_number (
  2         case
  3         when
  4            replace(translate(mixed,'0123456789','9999999999'),'9')
  5           is not null
  6         then
  7              replace(
  8            translate(mixed,
  9              replace(
 10            translate(mixed,'0123456789','9999999999'),'9'),
 11                     rpad('#',length(mixed),'#')),'#')
 12         else
 13              mixed
 14         end
 15         ) mixed
 16   from V
 17  where instr(translate(mixed,'0123456789','9999999999'),'9') > 0

PostgreSQL

Use functions TRANSLATE, REPLACE, and STRPOS to isolate the numeric characters in each row. The calls to CAST are not necessary in view V. Use the function REPLACE ito remove extraneous white space due to casting to the fixed length CHAR. If you decide you would like to keep the explicit type conversion calls in the view definition, it is suggested you cast to VARCHAR:

  1 select cast(
  2        case
  3        when
  4         replace(translate(mixed,'0123456789','9999999999'),'9','')
  5         is not null
  6        then
  7           replace(
  8        translate(mixed,
  9           replace(
 10        translate(mixed,'0123456789','9999999999'),'9',''),
 11                 rpad('#',length(mixed),'#')),'#','')
 12        else
 13          mixed
 14        end as integer ) as mixed
 15    from V
 16  where strpos(translate(mixed,'0123456789','9999999999'),'9') > 0

SQL Server

The built-in function ISNUMERIC along with a wildcard search allows you to easily identify strings that contains numbers, but getting numeric characters out of a string is not particularly efficient because the TRANSLATE function is not supported.

Discussion

The TRANSLATE function is very useful here as it allows you to easily isolate and identify numbers and characters. The trick is to convert all numbers to a single character; this way, rather than searching for different numbers you only search for one character.

DB2, Oracle, and PostgreSQL

The syntax differs slightly among these DBMSs, but the technique is the same. I'll use the solution for PostgreSQL for the discussion.

The real work is done by functions TRANSLATE and REPLACE. To get the final result set requires several function calls, each listed below in one query:

 
 select mixed as orig,
 translate(mixed,'0123456789','9999999999') as mixed1,
 replace(translate(mixed,'0123456789','9999999999'),'9','') as mixed2,
  translate(mixed,
  replace(
  translate(mixed,'0123456789','9999999999'),'9',''),
           rpad('#',length(mixed),'#')) as mixed3,
  replace(
  translate(mixed,
  replace(
 translate(mixed,'0123456789','9999999999'),'9',''),
           rpad('#',length(mixed),'#')),'#','') as mixed4
   from V
  where strpos(translate(mixed,'0123456789','9999999999'),'9') > 0

   ORIG  | MIXED1 | MIXED2 | MIXED3 | MIXED4 | MIXED5 
 --------+--------+--------+--------+--------+--------
  CL10AR | CL99AR | CLAR   | ##10## | 10     |     10
  KI10NG | KI99NG | KING   | ##10## | 10     |     10
  MI10LL | MI99LL | MILL   | ##10## | 10     |     10
  7369   | 9999   |        | 7369   | 7369   |   7369
  7566   | 9999   |        | 7566   | 7566   |   7566
  7788   | 9999   |        | 7788   | 7788   |   7788
  7876   | 9999   |        | 7876   | 7876   |   7876
  7902   | 9999   |        | 7902   | 7902   |   7902

First, notice that any rows without at least one number are removed. How this is accomplished will become clear as you examine each of the columns in the above result set. The rows that are kept are the values in the ORIG column and are the rows that will eventually make up the result set. The first step to extracting the numbers is to use the function TRANSLATE to convert any number to a 9 (you can use any digit; 9 is arbitrary), this is represented by the values in MIXED1. Now that all numbers are 9's, they can be treating as a single unit. The next step is to remove all of the numbers by using the function REPLACE. Because all digits are now 9, REPLACE simply looks for any 9's and removes them. This is represented by the values in MIXED2. The next step, MIXED3, uses values that are returned by MIXED2. These values are then compared to the values in ORIG. If any characters from MIXED2 are found in ORIG, they are converted to the # character by TRANSLATE. The result set from MIXED3 shows that the letters, not the numbers, have now been singled out and converted to a single character. Now that all non-numeric characters are represented by #'s, they can be treated as a single unit. The next step, MIXED4, uses REPLACE to find and remove any # characters in each row; what's left are numbers only. The final step is to cast the numeric characters as numbers. Now that you've gone through the steps, you can see how the WHERE clause works. The results from MIXED1 are passed to STRPOS, and if a 9 is found (the position in the string where the first 9 is located) the result must be greater than 0. For rows that return a value greater than zero, it means there's at least one number in that row and it should be kept.

MySQL

The first step is to walk each string and evaluate each character and determine whether or not it's a number:

 
 select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c
   from V,
       ( select id pos from t10 ) iter
  where iter.pos <= length(v.mixed)
  order by 1,2

 +--------+------+------+
 | mixed  | pos  | c    |
 +--------+------+------+
 | 7369   |    1 | 7    |
 | 7369   |    2 | 3    |
 | 7369   |    3 | 6    |
 | 7369   |    4 | 9    |
 …
 | ALLEN  |    1 | A    |
 | ALLEN  |    2 | L    |
 | ALLEN  |    3 | L    |
 | ALLEN  |    4 | E    |
 | ALLEN  |    5 | N    |
 …
 | CL10AR |    1 | C    |
 | CL10AR |    2 | L    |
 | CL10AR |    3 | 1    |
 | CL10AR |    4 | 0    |
 | CL10AR |    5 | A    |
 | CL10AR |    6 | R    |
 +--------+------+------+

Now that each character in each string can be evaluated individually, the next step is to keep only the rows that have a number in the C column:

 
 select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c
    from V,
        ( select id pos from t10 ) iter
  where iter.pos <= length(v.mixed)
   and ascii(substr(v.mixed,iter.pos,1)) between 48 and 57
  order by 1,2

 +--------+------+------+
 | mixed  | pos  | c    |
 +--------+------+------+
 | 7369   |    1 | 7    |
 | 7369   |    2 | 3    |
 | 7369   |    3 | 6    |
 | 7369   |    4 | 9    |
 …
 | CL10AR |    3 | 1    |
 | CL10AR |    4 | 0    |
 …
 +--------+------+------+

At this point, all the rows in column C are numbers. The next step is to use GROUP_CONCAT to concatenate the numbers to form their respective whole number in MIXED. The final result is then cast as a number:

 
 select cast(group_concat(c order by pos separator '') as unsigned)
          as MIXED1
   from ( 
 select v.mixed, iter.pos, substr(v.mixed,iter.pos,1) as c 
   from V, 
       ( select id pos from t10 ) iter 
  where iter.pos <= length(v.mixed) 
   and ascii(substr(x.mixed,iter.pos,1)) between 48 and 57
        ) y
   group by mixed
   order by 1

 +--------+
 | MIXED1 |
 +--------+
 |    10  |
 |    10  |
 |    10  |
 |  7369  |
 |  7566  |
 |  7788  |
 |  7876  |
 |  7902  |
 +--------+

As a final note, keep in mind that any digits in each string will be concatenated to form one numeric value. For example, an input value of, say, '99Gennick87' will result in the value 9987 being returned. This is something to keep in mind, particularly when working with serialized data.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Alphabetizing a String : SQL SERVER

2008-06-20T02:47:00.000-07:00

Problem

You want alphabetize the individual characters within strings in your tables. Consider the following result set:

 ENAME
 ----------
 ADAMS
 ALLEN
 BLAKE
 CLARK
 FORD
 JAMES
 JONES
 KING
 MARTIN
 MILLER
 SCOTT
 SMITH
 TURNER
 WARD

You would like the result to be:

 OLD_NAME   NEW_NAME
 ---------- --------
 ADAMS      AADMS
 ALLEN      AELLN
 BLAKE      ABEKL
 CLARK      ACKLR
 FORD       DFOR
 JAMES      AEJMS
 JONES      EJNOS
 KING       GIKN
 MARTIN     AIMNRT
 MILLER     EILLMR
 SCOTT      COSTT
 SMITH      HIMST
 TURNER     ENRRTU
 WARD       ADRW

Solution

This problem is a perfect example of why it is crucial to understand your DBMS and what functionality is available to you. In situations where your DBMS does not provide built-in functions to facilitate this solution, you need to come up with something creative. Compare the MySQL solution with the rest.

DB2

To alphabetize rows of strings it is necessary to walk each string then order its characters:

  1 select ename,
  2        max(case when pos=1 then c else '' end)||
  3        max(case when pos=2 then c else '' end)||
  4        max(case when pos=3 then c else '' end)||
  5        max(case when pos=4 then c else '' end)||
  6        max(case when pos=5 then c else '' end)||
  7        max(case when pos=6 then c else '' end)
  8   from (
  9 select e.ename,
 10        cast(substr(e.ename,iter.pos,1) as varchar(100)) c,
 11        cast(row_number( )over(partition by e.ename
 12                               order by substr(e.ename,iter.pos,1))
 13            as integer) pos
 14   from emp e,
 15        (select cast(row_number( )over( ) as integer) pos
 16           from emp) iter
 17  where iter.pos <= length(e.ename)
 18        ) x
 19  group by ename

MySQL

The key here is the GROUP_CONCAT function, which allows you to not only concatenate the characters that make up each name but also order them:

 1 select ename, group_concat(c order by c separator '')
 2   from (
 3 select ename, substr(a.ename,iter.pos,1) c
 4   from emp a,
 5        ( select id pos from t10 ) iter
 6  where iter.pos <= length(a.ename)
 7        ) x
 8  group by ename

Oracle

The function SYS_CONNECT_BY_PATH allows you to iteratively build a list:

  1 select old_name, new_name
  2   from (
  3 select old_name, replace(sys_connect_by_path(c,' '),' ') new_name
  4   from (
  5 select e.ename old_name,
  6        row_number() over(partition by e.ename
  7                         order by substr(e.ename,iter.pos,1)) rn,
  8        substr(e.ename,iter.pos,1) c
  9   from emp e,
 10        ( select rownum pos from emp ) iter
 11  where iter.pos <= length(e.ename)
 12  order by 1
 13         ) x
 14  start with rn = 1
 15 connect by prior rn = rn-1 and prior old_name = old_name
 16         )
 17  where length(old_name) = length(new_name)

PostgreSQL

PostgreSQL does not offer any built-in functions to easily sort characters in a string, so it is necessary not only to walk through each string but also to know in advance the largest length of any one name. View V is used in this solution for readability:

  create or replace view V as
  select x.*
    from (
  select a.ename,
         substr(a.ename,iter.pos,1) as c
    from emp a,
         (select id as pos from t10) iter
   where iter.pos <= length(a.ename)
   order by 1,2
         ) x

The following select statement leverages the view:

  1 select ename,
  2        max(case when pos=1 then
  3                 case when cnt=1 then c
  4                      else rpad(c,cast(cnt as integer),c)
  5                 end
  6                 else ''
  7             end)||
  8        max(case when pos=2 then
  9                 case when cnt=1 then c
 10                      else rpad(c,cast(cnt as integer),c)
 11                 end
 12                 else ''
 13             end)||
 14        max(case when pos=3 then
 15                 case when cnt=1 then c
 16                      else rpad(c,cast(cnt as integer),c)
 17                 end
 18                 else ''
 19             end)||
 20        max(case when pos=4 then
 21                 case when cnt=1 then c
 22                      else rpad(c,cast(cnt as integer),c)
 23                 end
 24                 else ''
 25             end)||
 26        max(case when pos=5 then
 27                 case when cnt=1 then c
 28                      else rpad(c,cast(cnt as integer),c)
 29                 end
 30                 else ''
 31             end)||
 32        max(case when pos=6 then
 33                 case when cnt=1 then c
 34                      else rpad(c,cast(cnt as integer),c)
 35                 end
 36                 else ''
 37             end)
 38   from (
 39 select a.ename, a.c,
 40        (select count(*)
 41           from v b
 42          where a.ename=b.ename and a.c=b.c ) as cnt,
 43        (select count(*)+1
 44           from v b
 45          where a.ename=b.ename and b.c

SQL Server

To alphabetize rows of strings it is necessary to walk each string, and then order their characters:

  1 select ename,
  2           max(case when pos=1 then c else '' end)+
  3           max(case when pos=2 then c else '' end)+
  4           max(case when pos=3 then c else '' end)+
  5           max(case when pos=4 then c else '' end)+
  6           max(case when pos=5 then c else '' end)+
  7           max(case when pos=6 then c else '' end)
  8      from (
  9    select e.ename,
 10         substring(e.ename,iter.pos,1) as c,
 11         row_number() over (
 12          partition by e.ename
 13              order by substring(e.ename,iter.pos,1)) as pos
 14    from emp e,
 15         (select row_number()over(order by ename) as pos
 16            from emp) iter
 17   where iter.pos <= len(e.ename)  18          ) x  19   group by ename

Discussion

DB2 and SQL Server

The inline view X returns each character in each name as a row. The function SUBSTR or SUBSTRING extracts each character from each name, and the function ROW_NUMBER ranks each character alphabetically:

 ENAME  C  POS
 -----  -  ---
 ADAMS  A  1
 ADAMS  A  2
 ADAMS  D  3
 ADAMS  M  4
 ADAMS  S  5
 …

To return each letter of a string as a row, you must walk the string. This is accomplished with inline view ITER.

Now that the letters in each name have been alphabetized, the last step is to put those letters back together, into a string, in the order they are ranked. Each letter's position is evaluated by the CASE statements (lines 27). If a character is found at a particular position it is then concatenated to the result of the next evaluation (the following CASE statement). Because the aggregate function MAX is used as well, only one character per position POS is returned, so that only one row per name is returned. The CASE evaluation goes up to the number 6, which is the maximum number of characters in any name in table EMP.

MySQL

The inline view X (lines 36) returns each character in each name as a row. The function SUBSTR extracts each character from each name:

 ENAME  C
 -----  -
 ADAMS  A
 ADAMS  A
 ADAMS  D
 ADAMS  M
 ADAMS  S
 …

Inline view ITER is used to walk the string. From there, the rest of the work is done by the GROUP_CONCAT function. By specifying an order, the function not only concatenates each letter, it does so alphabetically.

Oracle

The real work is done by inline view X (lines 511), where the characters in each name are extracted and put into alphabetical order. This is accomplished by walking the string, then imposing order on those characters. The rest of the query merely glues the names back together.

The tearing apart of names can be seen by executing only inline view X:

 OLD_NAME          RN C
 ---------- --------- -
 ADAMS              1 A
 ADAMS              2 A
 ADAMS              3 D
 ADAMS              4 M
 ADAMS              5 S
 …

The next step is to take the alphabetized characters and rebuild each name. This is done with the function SYS_CONNECT_BY_PATH by appending each character to the ones before it:

 OLD_NAME   NEW_NAME
 ---------- ---------
 ADAMS      A
 ADAMS      AA
 ADAMS      AAD
 ADAMS      AADM
 ADAMS      AADMS
 …

The final step is to keep only the strings that have the same length as the names they were built from.

PostgreSQL

For readability, view V is used in this solution to walk the string. The function SUBSTR, in the view definition, extracts each character from each name so that the view returns:

 ENAME C
 ----- -
 ADAMS A
 ADAMS A
 ADAMS D
 ADAMS M
 ADAMS S
 …

The view also orders the results by ENAME and by each letter in each name. The inline view X (lines 1518) returns the names and characters from view V, the number of times each character occurs in each name, and its position (alphabetically):

 ename | c | cnt | pos
 ------+---+-----+-----
 ADAMS | A |   2 |   1
 ADAMS | A |   2 |   1
 ADAMS | D |   1 |   3
 ADAMS | M |   1 |   4
 ADAMS | S |   1 |   5

The extra columns CNT and POS, returned by the inline view X, are crucial to the solution. POS is used to rank each character and CNT is used to determine the number of times the character exists in each name. The final step is to evaluate the position of each character and rebuild the name. You'll notice that each case statement is actually two case statements. This is to determine whether or not a character occursmore than once in a name; if it does, then rather than return that character, what is returned is that character appended to itself CNT times. The aggregate function, MAX, is used to ensure there is only one row per name.** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Converting Delimited Data into a Multi-Valued IN-List : SQL Server

2008-06-20T02:44:00.000-07:00

Problem

You have delimited data that you want to pass to the IN-list iterator of a WHERE clause. Consider the following string:

 7654,7698,7782,7788

You would like to use the string in a WHERE clause but the following SQL fails because EMPNO is a numeric column:

 select ename,sal,deptno
   from emp
  where empno in ( '7654,7698,7782,7788' )

This SQL fails because, while EMPNO is a numeric column, the IN list is composed of a single string value. You want that string to be treated as a comma-delimited list of numeric values.

Solution

On the surface it may seem that SQL should do the work of treating a delimited string as a list of delimited values for you, but that is not the case. When a comma embedded within quotes is encountered, SQL can't possibly know that signals a multi-valued list. SQL must treat everything between the quotes as a single entity, as one string value. You must break the string up into individual EMPNOs. The key to this solution is to walk the string, but not into individual characters. You want to walk the string into valid EMPNO values.

DB2

By walking the string passed to the IN-list, you can you can easily convert it to rows. The functions ROW_NUMBER, LOCATE, and SUBSTR are particularly useful here:

  1 select empno,ename,sal,deptno
  2   from emp
  3  where empno in (
  4 select cast(substr(c,2,locate(',',c,2)-2) as integer) empno
  5   from (
  6 select substr(csv.emps,cast(iter.pos as integer)) as c
  7   from (select ','||'7654,7698,7782,7788'||',' emps
  8          from t1) csv,
  9        (select id as pos
 10           from t100 ) iter
 11  where iter.pos <= length(csv.emps)
 12        ) x
 13  where length(c) > 1
 14    and substr(c,1,1) = ','
 15        ) y

MySQL

By walking the string passed to the IN-list, you can easily convert it to rows:

  1 select empno, ename, sal, deptno 
  2   from emp
  3  where empno in
  4        (
  5 select substring_index(
  6        substring_index(list.vals,',',iter.pos),',',-1) empno
  6   from (select id pos from t10) as iter,
  7        (select '7654,7698,7782,7788' as vals
  8           from t1) list
  9  where iter.pos <=
 10        (length(list.vals)-length(replace(list.vals,',','')))+1
 11        ) x

Oracle

By walking the string passed to the IN-list, you can you can easily convert it to rows. The functions ROWNUM, SUBSTR, and INSTR are particularly useful here:

  1 select empno,ename,sal,deptno
  2   from emp
  3  where empno in (
  4        select to_number(
  5                   rtrim(
  6                 substr(emps,
  7                  instr(emps,',',1,iter.pos)+1,
  8                  instr(emps,',',1,iter.pos+1)
  9                  instr(emps,',',1,iter.pos)),',')) emps
 10          from (select ','||'7654,7698,7782,7788'||',' emps from t1) csv,
 11               (select rownum pos from emp) iter
 12         where iter.pos <= ((length(csv.emps)-  13                   length(replace(csv.emps,',')))/length(','))-1  14 )

Postgres

By walking the string passed to the IN-list, you can easily convert it to rows. The function SPLIT_PART makes it easy to parse the string into individual numbers:

  1 select ename,sal,deptno
  2   from emp
  3  where empno in (
  4 select cast(empno as integer) as empno
  5   from (
  6 select split_part(list.vals,',',iter.pos) as empno
  7   from (select id as pos from t10) iter,
  8        (select ','||'7654,7698,7782,7788'||',' as vals
  9           from t1) list
 10  where iter.pos <=  11        length(list.vals)-length(replace(list.vals,',',''))  12        ) z  13  where length(empno) > 0
 14        ) x

SQL Server

By walking the string passed to the IN-list, you can you can easily convert it to rows. The functions ROW_NUMBER, CHARINDEX, and SUBSTRING are particularly useful here:

  1 select empno,ename,sal,deptno
  2   from emp
  3  where empno in (select substring(c,2,charindex(',',c,2)-2) as empno
  4   from (
  5 select substring(csv.emps,iter.pos,len(csv.emps)) as c
  6   from (select ','+'7654,7698,7782,7788'+',' as emps
  7           from t1) csv,
  8        (select id as pos
  9          from t100) iter
 10  where iter.pos <= len(csv.emps)  11       ) x  12  where len(c) > 1
 13    and substring(c,1,1) = ','
 14       ) y

Discussion

The first and most important step in this solution is to walk the string. Once you've accomplished that, all that's left is to parse the string into individual, numeric values using your DBMS's provided functions.

DB2 and SQL Server

The inline view X (lines 611) walks the string. The idea in this solution is to "walk through" the string, so that each row has one less character than the one before it:

 ,7654,7698,7782,7788,
 7654,7698,7782,7788,
 654,7698,7782,7788,
 54,7698,7782,7788,
 4,7698,7782,7788,
 ,7698,7782,7788,
 7698,7782,7788,
 698,7782,7788,
 98,7782,7788,
 8,7782,7788,
 ,7782,7788,
 7782,7788,
 782,7788,
 82,7788,
 2,7788,
 ,7788,
 7788,
 788,
 88,
 8,
 ,

Notice that by enclosing the string in commas (the delimiter), there's no need to make special checks as to where the beginning or end of the string is.

The next step is to keep only the values you want to use in the IN-list. The values to keep are the ones with leading commas, with the exception of the last row with its lone comma. Use SUBSTR or SUBSTRING to identify which rows have a leading comma, then keep all characters found before the next comma in that row. Once that's done, cast the string to a number so it can be properly evaluated against the numeric column EMPNO (lines 414):

  EMPNO
 ------
   7654
   7698
   7782
   7788

The final step is to use the results in a subquery to return the desired rows.

MySQL

The inline view (lines 59) walks the string. The expression on line 10 determines how many values are in the string by finding the number of commas (the delimiter) and adding one. The function SUBSTRING_INDEX (line 6) returns all characters in the string before (to the left of ) the nth occurrence of a comma (the delimiter):

 +---------------------+
 | empno               |
 +---------------------+
 | 7654                |
 | 7654,7698           |
 | 7654,7698,7782      |
 | 7654,7698,7782,7788 |
 +---------------------+

Those rows are then passed to another call to SUBSTRING_INDEX (line 5); this time the nth occurrence of the delimited is 1, which causes all values to the right of the nth occurrence of the delimiter to be kept:

 +-------+
 | empno |
 +-------+
 | 7654  |
 | 7698  |
 | 7782  |
 | 7788  |
 +-------+

The final step is to plug the results into a subquery.

Oracle

The first step is to walk the string:

 
 select emps,pos
   from (select ','||'7654,7698,7782,7788'||',' emps
           from t1) csv,
        (select rownum pos from emp) iter
  where iter.pos <=   ((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1

 EMPS                         POS
 --------------------- ----------
 ,7654,7698,7782,7788,          1
 ,7654,7698,7782,7788,          2
 ,7654,7698,7782,7788,          3
 ,7654,7698,7782,7788,          4

The number of rows returned represents the number of values in your list. The values for POS are crucial to the query as they are needed to parse the string into individual values. The strings are parsed using SUBSTR and INSTR. POS is used to locate the nth occurrence of the delimiter in each string. By enclosing the strings in commas, no special checks are necessary to determine the beginning or end of a string. The values passed to SUBSTR, INSTR (lines 79) locate the nth and nth+1 occurrence of the delimiter. By subtracting the value returned for the current comma (the location in the string where the current comma is) from the value returned bythe next comma (the location in the string where the next comma is) you can extract each value from the string:

 
 select substr(emps,
        instr(emps,',',1,iter.pos)+1,
        instr(emps,',',1,iter.pos+1)
        instr(emps,',',1,iter.pos)) emps
   from (select ','||'7654,7698,7782,7788'||',' emps
           from t1) csv,
        (select rownum pos from emp) iter
  where iter.pos <=
   ((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1
  EMPS
  -----------
  7654,
  7698,
  7782,
  7788,

The final step is to remove the trailing comma from each value, cast it to a number, and plug it into a subquery.

PostgreSQL

The inline view Z (lines 69) walks the string. The number of rows returned is determined by how many values are in the string. To find the number of values in the string, subtract the size of the string without the delimiter from the size of the string with the delimiter (line 9). The function SPLIT_PART does the work of parsing the string. It looks for the value that comes before the nth occurrence of the delimiter:

 
 select list.vals,
        split_part(list.vals,',',iter.pos) as empno,
        iter.pos
   from (select id as pos from t10) iter,
        (select ','||'7654,7698,7782,7788'||',' as vals
           from t1) list
  where iter.pos <=         length(list.vals)-length(replace(list.vals,',',''))

      vals             | empno | pos
 ----------------------+-------+-----
 ,7654,7698,7782,7788, |       |   1
 ,7654,7698,7782,7788, | 7654  |   2
 ,7654,7698,7782,7788, | 7698  |   3
 ,7654,7698,7782,7788, | 7782  |   4
 ,7654,7698,7782,7788, | 7788  |   5

The final step is to cast the values (EMPNO) to a number and plug it into a subquery.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server : Creating a Delimited List from Table Rows

2008-06-17T22:04:00.000-07:00

Problem

You want to return table rows as values in a delimited list, perhaps delimited by commas, rather than in vertical columns as they normally appear. You want to convert a result set from this:

 DEPTNO EMPS
 ------ ----------
     10 CLARK
     10 KING
     10 MILLER
     20 SMITH
     20 ADAMS
     20 FORD
     20 SCOTT
     20 JONES
     30 ALLEN
     30 BLAKE
     30 MARTIN
     30 JAMES
     30 TURNER
     30 WARD

to this:

  DEPTNO EMPS
 ------- ------------------------------------
      10 CLARK,KING,MILLER
      20 SMITH,JONES,SCOTT,ADAMS,FORD
      30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

Solution

Each DBMS requires a different approach to this problem. The key is to take advantage of the built-in functions provided by your DBMS. Understanding what is available to you will allow you to exploit your DBMS's functionality and come up with creative solutions for a problem that is typically not solved in SQL.

DB2

Use recursive WITH to build the delimited list:

  1   with x (deptno, cnt, list, empno, len)
  2     as (
  3 select deptno, count(*) over (partition by deptno),
  4        cast(ename as varchar(100)), empno, 1
  5   from emp
  6  union all
  7  select x.deptno, x.cnt, x.list ||','|| e.ename, e.empno, x.len+1
  8   from emp e, x
  9  where e.deptno = x.deptno
 10    and e.empno > x. empno
 11        )
 12 select deptno,list
 13   from x
 14  where len = cnt

MySQL

Use the built-in function GROUP_CONCAT to build the delimited list:

 1 select deptno,
 2        group_concat(ename order by empno separator, ',') as emps
 3   from emp
 4  group by deptno

Oracle

Use the built-in function SYS_CONNECT_BY_PATH to build the delimited list:

  1 select deptno,
  2        ltrim(sys_connect_by_path(ename,','),',') emps
  3   from (
  4 select deptno,
  5        ename,
  6        row_number() over
  7                 (partition by deptno order by empno) rn,
  8        count(*) over
  9                 (partition by deptno) cnt
 10   from emp
 11        )
 12  where level = cnt
 13  start with rn = 1
 14 connect by prior deptno = deptno and prior rn = rn-1

PostgreSQL

PostgreSQL does not offer a standard built-in function for creating a delimited list, so it is necessary to know how many values will be in the list in advance. Once you know the size of the largest list, you can determine the number of values to append to create your list by using standard transposition and concatenation:

  1 select deptno,
  2        rtrim(
  3            max(case when pos=1 then emps else '' end)||
  4            max(case when pos=2 then emps else '' end)||
  5            max(case when pos=3 then emps else '' end)||
  6            max(case when pos=4 then emps else '' end)||
  7            max(case when pos=5 then emps else '' end)||
  8            max(case when pos=6 then emps else '' end),','
  9        ) as emps
 10   from (
 11 select a.deptno,
 12        a.ename||',' as emps,
 13        d.cnt,
 14        (select count(*) from emp b
 15           where a.deptno=b.deptno and b.empno <= a.empno) as pos
 16   from emp a,
 17        (select deptno, count(ename) as cnt
 18           from emp
 19           group by deptno) d
 20  where d.deptno=a.deptno
 21        ) x
 22  group by deptno
 23  order by 1

SQL Server

Use recursive WITH to build the delimited list:

  1  with x (deptno, cnt, list, empno, len)
  2     as (
  3 select deptno, count(*) over (partition by deptno),
  4        cast(ename as varchar(100)),
  5        empno,
  6        1
  7   from emp
  9  union all
  9 select x.deptno, x.cnt,
 10        cast(x.list + ',' + e.ename as varchar(100)),
 11        e.empno, x.len+1
 12   from emp e, x
 13 where e.deptno = x.deptno
 14   and e.empno > x. empno
 15            )
 16 select deptno,list
 17   from x
 18  where len = cnt
 19  order by 1

Discussion

Being able to create delimited lists in SQL is useful because it is a common requirement. Yet each DBMS offers a unique method for building such a list in SQL. There's very little commonality between the vendor-specific solutions; the techniques vary from using recursion, to hierarchal functions, to classic transposition, to aggregation.

DB2 and SQL Server

The solution for these two databases differ slightly in syntax (the concatenation operators are "||" for DB2 and "+" for SQL Server), but the technique is the same. The first query in the WITH clause (upper portion of the UNION ALL) returns the following information about each employee: the department, the number of employees in that department, the name, the ID, and a constant 1 (which at this point doesn't do anything). Recursion takes place in the second query (lower half of the UNION ALL) to build the list. To understand how the list is built, examine the following excerpts from the solution: first, the third SELECT-list item from the second query in the union:

 x.list ||','|| e.ename

and then the WHERE clause from that same query:

 where e.deptno = x.deptno
   and e.empno > x.empno

The solution works by first ensuring the employees are in the same department. Then, for every employee returned by the upper portion of the UNION ALL, append the name of the employees who have a greater EMPNO. By doing this, you ensure that no employee will have his own name appended. The expression

 x.len+1

increments LEN (which starts at 1) every time an employee has been evaluated. When the incremented value equals the number of employees in the department:

 where len = cnt

you know you have evaluated all the employees and have completed building the list. That is crucial to the query as it not only signals when the list is complete, but also stops the recursion from running longer than necessary.

MySQL

The function GROUP_CONCAT does all the work. It concatenates the values found in the column passed to it, in this case ENAME. It's an aggregate function, thus the need for GROUP BY in the query.

Oracle

The first step to understanding the Oracle query is to break it down. Running the inline view by itself (lines 410), you generate a result set that includes the following for each employee: her department, her name, a rank within her respective department that is derived by an ascending sort on EMPNO, and a count of all employees in her department. For example:

 
 select deptno,
         ename,
         row_number() over
                   (partition by deptno order by empno) rn,
         count(*) over (partition by deptno) cnt
   from emp

 DEPTNO ENAME      RN CNT
 ------ ---------- -- ---
     10 CLARK       1   3
     10 KING        2   3
     10 MILLER      3   3
     20 SMITH       1   5
     20 JONES       2   5
     20 SCOTT       3   5
     20 ADAMS       4   5
     20 FORD        5   5
     30 ALLEN       1   6
     30 WARD        2   6
     30 MARTIN      3   6
     30 BLAKE       4   6
     30 TURNER      5   6
     30 JAMES       6   6

The purpose of the rank (aliased RN in the query) is to allow you to walk the tree. Since the function ROW_NUMBER generates an enumeration starting from one with no duplicates or gaps, just subtract one (from the current value) to reference a prior (or parent) row. For example, the number prior to 3 is 3 minus 1, which equals 2. In this context, 2 is the parent of 3; you can observe this on line 12. Additionally, the lines

 start with rn = 1
 connect by prior deptno = deptno

identify the root for each DEPTNO as having RN equal to 1 and create a new list whenever a new department is encountered (whenever a new occurrence of 1 is found for RN).

At this point, it's important to stop and look at the ORDER BY portion of the ROW_NUMBER function. Keep in mind the names are ranked by EMPNO and the list will be created in that order. The number of employees per department is calculated (aliased CNT) and is used to ensure that the query returns only the list that has all the employee names for a department. This is done because SYS_CONNECT_ BY_PATH builds the list iteratively, and you do not want to end up with partial lists.

For heirarchical queries, the pseudocolumn LEVEL starts with 1 (for queries not using CONNECT BY, LEVEL is 0, unless you are on 10g and later when LEVEL is only available when using CONNECT BY) and increments by one after each employee in a department has been evaluated (for each level of depth in the hierarchy). Because of this, you know that once LEVEL reaches CNT, you have reached the last EMPNO and will have a complete list.

PostgreSQL

PostgreSQL's solution requires you to know in advance the maximum number of employees in any one department. Running the inline view by itself (lines 1118) generates a result set that includes (for each employee) his department, his name with a comma appended, the number of employees in his department, and the number of employees who have an EMPNO that is less than his:

 deptno  |  emps    | cnt | pos
 --------+----------+-----+-----
     20  |  SMITH,  |   5 |   1
     30  |  ALLEN,  |   6 |   1
     30  |  WARD,   |   6 |   2
     20  |  JONES,  |   5 |   2
     30  |  MARTIN, |   6 |   3
     30  |  BLAKE,  |   6 |   4
     10  |  CLARK,  |   3 |   1
     20  |  SCOTT,  |   5 |   3
     10  |  KING,   |   3 |   2
     30  |  TURNER, |   6 |   5
     20  |  ADAMS,  |   5 |   4
     30  |  JAMES,  |   6 |   6
     20  |  FORD,   |   5 |   5
     10  |  MILLER, |   3 |   3

The scalar subquery, POS (lines 14-15), is used to rank each employee by EMPNO. For example, the line

 max(case when pos = 1 then ename else '' end)||

evaluates whether or not POS equals 1. The CASE expression returns the employee name when POS is 1, and otherwise returns NULL.

You must query your table first to find the largest number of values that could be in any one list. Based on the EMP table, the largest number of employees in any one department is six, so the largest number of items in a list is six.

The next step is to begin creating the list. Do this by performing some conditional logic (in the form of CASE expressions) on the rows returned from the inline view.

You must write as many CASE expressions as there are possible values to be concatenated together.

If POS equals one, the current name is added to the list. The second CASE expression evaluates whether or not POS equals two; if it does, then the second name is appended to the first. If there is no second name, then an additional comma is appended to the first name (this process is repeated for each distinct value of POS until the last one is reached).

The use of the MAX function is necessary because you want to build only one list per department (you can also use MIN; it makes no difference in this case, since POS returns only one value for each case evaluation). Whenever an aggregate function is used, any items in the SELECT list not acted upon by the aggregate must be specified in the GROUP BY clause. This guarantees you will have only one row per item in the SELECT list not acted upon by the aggregate function.

Notice that you also need the function RTRIM to remove trailing commas; the number of commas will always be equal to the maximum number of values that could potentially be in a list (in this case, six).

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL - Ordering by a Number in a String

2008-06-17T03:11:00.000-07:00

Problem

You want order your result set based on a number within a string. Consider the following view:

 create view V as
 select e.ename ||' '||
         cast(e.empno as char(4))||' '||
         d.dname as data
   from emp e, dept d
  where e.deptno=d.deptno

This view returns the following data:

 DATA
  ----------------------------
  CLARK   7782 ACCOUNTING
  KING    7839 ACCOUNTING
  MILLER  7934 ACCOUNTING
  SMITH   7369 RESEARCH
  JONES   7566 RESEARCH
  SCOTT   7788 RESEARCH
  ADAMS   7876 RESEARCH
  FORD    7902 RESEARCH
  ALLEN   7499 SALES
  WARD    7521 SALES
  MARTIN  7654 SALES
  BLAKE   7698 SALES
  TURNER  7844 SALES
  JAMES   7900 SALES

You want to order the results based on the employee number, which falls between the employee name and respective department:

 DATA
 ---------------------------
 SMITH    7369 RESEARCH
 ALLEN    7499 SALES
 WARD     7521 SALES
 JONES    7566 RESEARCH
 MARTIN   7654 SALES
 BLAKE    7698 SALES
 CLARK    7782 ACCOUNTING
 SCOTT    7788 RESEARCH
 KING     7839 ACCOUNTING
 TURNER   7844 SALES
 ADAMS    7876 RESEARCH
 JAMES    7900 SALES
 FORD     7902 RESEARCH
 MILLER   7934 ACCOUNTING

Solution

Each solution uses functions and syntax specific to its DBMS, but the method (making use of the built-in functions REPLACE and TRANSLATE) is the same for each. The idea is to use REPLACE and TRANSLATE to remove non-digits from the strings, leaving only the numeric values upon which to sort.

DB2

Use the built-in functions REPLACE and TRANSLATE to order by numeric characters in a string:

 1 select data
 2   from V
 3  order by
 4         cast(
 5      replace(
 6    translate(data,repeat('#',length(data)),
 7      replace(
 8    translate(data,'##########','0123456789'),
 9             '#','')),'#','') as integer)

Oracle

Use the built-in functions REPLACE and TRANSLATE to order by numeric characters in a string:

 1 select data
 2   from V
 3  order by
 4         to_number(
 5           replace(
 6         translate(data,
 7           replace(
 8         translate(data,'0123456789','##########'),
 9                  '#'),rpad('#',20,'#')),'#'))

PostgreSQL

Use the built-in functions REPLACE and TRANSLATE to order by numeric characters in a string:

 1 select data
 2   from V
 3  order by
 4         cast(
 5      replace(
 6    translate(data,
 7      replace(
 8    translate(data,'0123456789','##########'),
 9             '#',''),rpad('#',20,'#')),'#','') as integer)

MySQL and SQL Server

As of the time of this writing, neither vendor supplies the TRANSLATE function.

Discussion

The purpose of view V is only to supply rows on which to demonstrate this recipe's solution. The view simply concatenates several columns from the EMP table. The solution shows how to take such concatenated text as input and sort it by the employee number embedded within.

The ORDER BY clause in each solution may look a bit intimidating but performs quite well and is pretty straightforward once you examine it piece by piece. To order by the numbers in the string, it's easiest to remove any characters that are not numbers. Once the non-numeric characters are removed all that is left to do is cast the string of numerals into a number, then sort as you see fit. Before examining each function call it is important to understand the order in which each function is called. Starting with the innermost call, TRANSLATE (line 8 from each of the original solutions), you see that:

TRANSLATE (line 8) is called and the results are returned to
REPLACE (line 7) and those results are returned to
TRANSLATE (line 6) and those results are returned to
REPLACE (line 5) and those results are returned and finally
cast into a number

The first step is to convert the numbers into characters that do not exist in the rest of the string. For this example, I chose "#" and used TRANSLATE to convert all non-numeric characters into occurrences of "#". For example, the following query shows the original data on the left and the results from the first translation:

 
 select data,
        translate(data,'0123456789','##########') as tmp
   from V

 DATA                           TMP
  ------------------------------ -----------------------
  CLARK   7782 ACCOUNTING        CLARK   #### ACCOUNTING
  KING    7839 ACCOUNTING        KING    #### ACCOUNTING
  MILLER  7934 ACCOUNTING        MILLER  #### ACCOUNTING
  SMITH   7369 RESEARCH          SMITH   #### RESEARCH
  JONES   7566 RESEARCH          JONES   #### RESEARCH
  SCOTT   7788 RESEARCH          SCOTT   #### RESEARCH
  ADAMS   7876 RESEARCH          ADAMS   #### RESEARCH
  FORD    7902 RESEARCH          FORD    #### RESEARCH
  ALLEN   7499 SALES             ALLEN   #### SALES
  WARD    7521 SALES             WARD    #### SALES
  MARTIN  7654 SALES             MARTIN  #### SALES
  BLAKE   7698 SALES             BLAKE   #### SALES
  TURNER 7844 SALES          TURNER  #### SALES
  JAMES  7900 SALES          JAMES   #### SALES

TRANSLATE finds the numerals in each string and converts each one to to the "#" character. The modified strings are then returned to REPLACE (line 11), which removes all occurrences of "#":

 
 select data,
 replace(
 translate(data,'0123456789','##########'),'#') as tmp
   from V

 DATA                           TMP
  ------------------------------ -------------------
  CLARK   7782 ACCOUNTING        CLARK   ACCOUNTING
  KING    7839 ACCOUNTING        KING    ACCOUNTING
  MILLER  7934 ACCOUNTING        MILLER  ACCOUNTING
  SMITH   7369 RESEARCH          SMITH   RESEARCH
  JONES   7566 RESEARCH          JONES   RESEARCH
  SCOTT   7788 RESEARCH          SCOTT   RESEARCH
  ADAMS   7876 RESEARCH          ADAMS   RESEARCH
  FORD    7902 RESEARCH          FORD    RESEARCH
  ALLEN   7499 SALES             ALLEN   SALES
  WARD    7521 SALES             WARD    SALES
  MARTIN  7654 SALES             MARTIN  SALES
  BLAKE   7698 SALES             BLAKE   SALES
  TURNER  7844 SALES             TURNER  SALES
  JAMES   7900 SALES             JAMES   SALES

The strings are then returned to TRANSLATE once again, but this time it's the second (outermost) TRANSLATE in the solution. TRANSLATE searches the original string for any characters that match the characters in TMP. If any are found, they too are converted to "#"s. This conversion allows all non-numeric characters to be treated as a single character (because they are all transformed to the same character):

 
 select data, translate(data,
              replace(
              translate(data,'0123456789','##########'),
              '#'),
              rpad('#',length(data),'#')) as tmp
   from V

 DATA                           TMP
 ------------------------------ ---------------------------
 CLARK   7782 ACCOUNTING        ########7782###########
 KING    7839 ACCOUNTING        ########7839###########
 MILLER  7934 ACCOUNTING        ########7934###########
 SMITH   7369 RESEARCH          ########7369#########
 JONES   7566 RESEARCH          ########7566#########
 SCOTT   7788 RESEARCH          ########7788#########
 ADAMS   7876 RESEARCH          ########7876#########
 FORD    7902 RESEARCH          ########7902#########
 ALLEN   7499 SALES             ########7499######
 WARD    7521 SALES             ########7521######
 MARTIN  7654 SALES             ########7654######
 BLAKE   7698 SALES             ########7698######
 TURNER  7844 SALES             ########7844######
 JAMES   7900 SALES             ########7900######

The next step is to remove all "#" characters through a call to REPLACE (line 8), leaving you with only numbers:

 
 select data, replace(
              translate(data,
              replace(
            translate(data,'0123456789','##########'),
                      '#'),
                      rpad('#',length(data),'#')),'#') as tmp
   from V

 DATA                           TMP
 ------------------------------ -----------
 CLARK   7782 ACCOUNTING        7782
 KING    7839 ACCOUNTING        7839
 MILLER  7934 ACCOUNTING        7934
 SMITH   7369 RESEARCH          7369
 JONES   7566 RESEARCH          7566
 SCOTT   7788 RESEARCH          7788
 ADAMS   7876 RESEARCH          7876
 FORD    7902 RESEARCH          7902
 ALLEN   7499 SALES             7499
 WARD    7521 SALES             7521
 MARTIN  7654 SALES             7654
 BLAKE   7698 SALES             7698
 TURNER  7844 SALES             7844
 JAMES   7900 SALES             7900

Finally, cast TMP to a number (line 4) using the appropriate DBMS function (often CAST) to accomplish this:

 
 select data, to_number(
               replace(
              translate(data,
              replace(
        translate(data,'0123456789','##########'),
                  '#'),
                  rpad('#',length(data),'#')),'#')) as tmp
   from V

 DATA                                  TMP
 ------------------------------ ----------
 CLARK   7782 ACCOUNTING              7782
 KING    7839 ACCOUNTING              7839
 MILLER  7934 ACCOUNTING              7934
 SMITH   7369 RESEARCH                7369
 JONES   7566 RESEARCH                7566
 SCOTT   7788 RESEARCH                7788
 ADAMS   7876 RESEARCH                7876
 FORD    7902 RESEARCH                7902
 ALLEN   7499 SALES                   7499
 WARD    7521 SALES                   7521
 MARTIN  7654 SALES                   7654
 BLAKE   7698 SALES                   7698
 TURNER  7844 SALES                   7844
 JAMES   7900 SALES                   7900

When developing queries like this, it's helpful to work with your expressions in the SELECT list. That way, you can easily view the intermediate results as you work toward a final solution. However, because the point of this recipe is to order the results, ultimately you should place all the function calls into the ORDER BY clause:

 
 select data
   from V
  order by
         to_number(
           replace(
         translate( data,
           replace(
         translate( data,'0123456789','##########'),
                   '#'),rpad('#',length(data),'#')),'#'))

 DATA
 ---------------------------
 SMITH   7369 RESEARCH
 ALLEN   7499 SALES
 WARD    7521 SALES
 JONES   7566 RESEARCH
 MARTIN  7654 SALES
 BLAKE   7698 SALES
 CLARK   7782 ACCOUNTING
 SCOTT   7788 RESEARCH
 KING    7839 ACCOUNTING
 TURNER  7844 SALES
 ADAMS   7876 RESEARCH
 JAMES   7900 SALES
 FORD    7902 RESEARCH
 MILLER  7934 ACCOUNTING

As a final note, the data in the view is comprised of three fields, only one being numeric. Keep in mind that if there had been multiple numeric fields, they would have all been concatenated into one number before the rows were sorted.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Ordering by Parts of a String : SQL

2008-06-17T03:10:00.000-07:00

Problem

You want to order your result set based on a substring. Consider the following records:

 ENAME
 ----------
 SMITH
 ALLEN
 WARD
 JONES
 MARTIN
 BLAKE
 CLARK
 SCOTT
 KING
 TURNER
 ADAMS
 JAMES
 FORD
 MILLER

You want the records to be ordered based on the last two characters of each name:

 ENAME
 ---------
 ALLEN
 TURNER
 MILLER
 JONES
 JAMES
 MARTIN
 BLAKE
 ADAMS
 KING
 WARD
 FORD
 CLARK
 SMITH
 SCOTT

Solution

The key to this solution is to find and use your DBMS's built-in function to extract the substring on which you wish to sort. This is typically done with the SUBSTR function.

DB2, Oracle, MySQL, and PostgreSQL

Use a combination of the built-in functions LENGTH and SUBSTR to order by a specific part of a string:

 1 select ename
 2   from emp
 3  order by substr(ename,length(ename)-1,)

SQL Server

Use functions SUBSTRING and LEN to order by a specific part of a string:

 1 select ename
 2   from emp
 3  order by substring(ename,len(ename)-1,2)

Discussion

By using a SUBSTR expression in your ORDER BY clause, you can pick any part of a string to use in ordering a result set. You're not limited to SUBSTR either. You can order rows by the result of almost any expression.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Extracting Initials from a Name - SQL SERVER

2008-06-13T01:30:00.000-07:00

Problem

You want convert a full name into initials. Consider the following name:

  Stewie Griffin

You would like to return:

  S.G.

Solution

It's important to keep in mind that SQL does not provide the flexibility of languages such as C or Python; therefore, creating a generic solution to deal with any name format is not something particularly easy to do in SQL. The solutions presented here expect the names to be either first and last name, or first, middle name/middle initial, and last name.

DB2

Use the built-in functions REPLACE, TRANSLATE, and REPEAT to extract the initials:

 1 select replace(
 2        replace(
 3        translate(replace('Stewie Griffin', '.', ''),
 4                  repeat('#',26),
 5                  'abcdefghijklmnopqrstuvwxyz'),
 6                   '#','' ), ' ','.' )
 7                  ||'.'
 8   from t1

MySQL

Use the built-in functions CONCAT, CONCAT_WS, SUBSTRING, and SUBSTRING_ INDEX to extract the initials:

  1 select case
  2          when cnt = 2 then
  3            trim(trailing '.' from
  4                 concat_ws('.',
  5                  substr(substring_index(name,' ',1),1,1),
  6                  substr(name,
  7                         length(substring_index(name,' ',1))+2,1),
  8                  substr(substring_index(name,' ',-1),1,1),
  9                  '.'))
 10          else
 11            trim(trailing '.' from
 12                 concat_ws('.',
 13                  substr(substring_index(name,' ',1),1,1),
 14                  substr(substring_index(name,' ',-1),1,1)
 15                  ))
 16          end as initials
 17   from (
 18 select name,length(name)-length(replace(name,' ','')) as cnt
 19   from (
 20 select replace('Stewie Griffin','.','') as name from t1
 21        )y
 22        )x

Oracle and PostgreSQL

Use the built-in functions REPLACE, TRANSLATE, and RPAD to extract the initials:

 1 select replace(
 2        replace(
 3        translate(replace('Stewie Griffin', '.', ''),
 4                  'abcdefghijklmnopqrstuvwxyz',
 5                  rpad('#',26,'#') ), '#','' ),' ','.' ) ||'.'
 6   from t1

SQL Server

As of the time of this writing, neither TRANSLATE nor CONCAT_WS is supported in SQL Server.

Discussion

By isolating the capital letters you can extract the initials from a name. The following sections describe each vendor-specific solution in detail.

DB2

The REPLACE function will remove any periods in the name (to handle middle initials), and the TRANSLATE function will convert all non-uppercase letters to #.

 
 select translate(replace('Stewie Griffin', '.', ''),
                  repeat('#',26),
                  'abcdefghijklmnopqrstuvwxyz')
   from t1

 TRANSLATE('STE
 --------------
 S##### G######

At this point, the initials are the characters that are not #. The function REPLACE is then used to remove all the # characters:

 
 select replace(
        translate(replace('Stewie Griffin', '.', ''),
                   repeat('#',26),
                   'abcdefghijklmnopqrstuvwxyz'),'#','')
   from t1
 
 REP
 ---
 S G

The next step is to replace the white space with a period by using REPLACE again:

 select replace(
        replace(
        translate(replace('Stewie Griffin', '.', ''),
                  repeat('#',26),
                 'abcdefghijklmnopqrstuvwxyz'),'#',''),' ','.') || '.'
   from t1

 REPLA
 -----
 S.G

The final step is to append a decimal to the end of the initials.

Oracle and PostgreSQL

The REPLACE function will remove any periods in the name (to handle middle initials), and the TRANSLATE function will convert all non-uppercase letters to '#'.

 
 select translate(replace('Stewie Griffin','.',''),
                  'abcdefghijklmnopqrstuvwxyz',
                  rpad('#',26,'#'))
   from t1
 
 TRANSLATE('STE
 --------------
 S##### G######

At this point, the initials are the characters that are not "#". The function REPLACE is then used to remove all the # characters:

 
 select replace(
        translate(replace('Stewie Griffin','.',''),
                  'abcdefghijklmnopqrstuvwxyz',
                   rpad('#',26,'#')),'#','')
   from t1

 REP
 ---
 S G

The next step is to replace the white space with a period by using REPLACE again:

 
 select replace(
        replace(
      translate(replace('Stewie Griffin','.',''),
                'abcdefghijklmnopqrstuvwxyz',
                rpad('#',26,'#') ),'#',''),' ','.') || '.'
   from t1

 REPLA
 -----
 S.G

The final step is to append a decimal to the end of the initials.

MySQL

The inline view Y is used to remove any period from the name. The inline view X finds the number of white spaces in the name so the SUBSTR function can be called the correct number of times to extract the initials. The three calls to SUBSTRING_ INDEX parse the string into individual names based on the location of the white space. Because there is only a first and last name, the code in the ELSE portion of the case statement is executed:

 
 select substr(substring_index(name, ' ',1),1,1) as a,
        substr(substring_index(name,' ',-1),1,1) as b
   from (select 'Stewie Griffin' as name from t1) x
 
 A B
 - -
 S G

If the name in question has a middle name or initial, the initial would be returned by executing

 substr(name,length(substring_index(name, ' ',1))+2,1)

which finds the end of the first name then moves two spaces to the beginning of the middle name or initial; that is, the start position for SUBSTR. Because only onecharacter is kept, the middle name or initial is successfully returned. The initials are then passed to CONCAT_WS, which separates the initials by a period:

 
 select concat_ws('.',
                  substr(substring_index(name, ' ',1),1,1),
                  substr(substring_index(name,' ',-1),1,1),
                  '.' ) a
   from (select 'Stewie Griffin' as name from t1) x
 
 A
 -----
 S.G..

The last step is to trim the extraneous period from the initials.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Determining Whether a String Is Alphanumeric : SQL

2008-06-13T01:28:00.000-07:00

Problem

You want to return rows from a table only when a column of interest contains no characters other than numbers and letters. Consider the following view V (SQL Server users will use the operator "+" for concatenation instead of "||"):

 create view V as
 select ename as data
   from emp
  where deptno=10
  union all
 select ename||', $'|| cast(sal as char(4)) ||'.00' as data
   from emp
  where deptno=20
  union all
 select ename|| cast(deptno as char(4)) as data
   from emp
  where deptno=30

The view V represents your table, and it returns the following:

 DATA
 --------------------
 CLARK
 KING
 MILLER
 SMITH, $800.00
 JONES, $2975.00
 SCOTT, $3000.00
 ADAMS, $1100.00
 FORD, $3000.00
 ALLEN30
 WARD30
 MARTIN30
 BLAKE30
 TURNER30
 JAMES30

However, from the view's data you want to return only the following records:

 DATA
 -------------
 CLARK
 KING
 MILLER
 ALLEN30
 WARD30
 MARTIN30
 BLAKE30
 TURNER30
 JAMES30

In short, you wish to omit those rows containing data other than letters and digits.

Solution

It may seem intuitive at first to solve the problem by searching for all the possible non-alphanumeric characters that can be found in a string, but, on the contrary, you will find it easier to do the exact opposite: find all the alphanumeric characters. By doing so, you can treat all the alphanumeric characters as one by converting them to one single character. The reason you want to do this is so the alphanumeric characters can be manipulated together, as a whole. Once you've generated a copy of the string in which all alphanumeric characters are represented by a single character of your choosing, it is easy to isolate the alphanumeric characters from any other characters.

DB2

Use the function TRANSLATE to convert all alphanumeric characters to a single character, then identify any rows that have characters other than the converted alphanumeric character. For DB2 users, the CAST function calls in view V are necessary; otherwise, the view cannot be created due to type conversion errors. Take extra care when working with casts to CHAR as they are fixed length (padded):

 1 select data
 2   from V
 3  where translate(lower(data),
 4                  repeat('a',36),
 5                  '0123456789abcdefghijklmnopqrstuvwxyz') =
 6                  repeat('a',length(data))

MySQL

The syntax for view V is slightly different in MySQL:

 create view V as
 select ename as data
   from emp
  where deptno=10
  union all
 select concat(ename,', $',sal,'.00') as data
   from emp
  where deptno=20
  union all
 select concat(ename,deptno) as data
   from emp
  where deptno=30

Use a regular expression to easily find rows that contain non-alphanumeric data:

 1 select data
 2   from V
 3  where data regexp '[^0-9a-zA-Z]' = 0

Oracle and PostgreSQL

Use the function TRANSLATE to convert all alphanumeric characters to a single character, then identify any rows that have characters other than the converted alphanumeric character. The CAST function calls in view V are not needed for Oracle and PostgreSQL. Take extra care when working with casts to CHAR as they are fixed length (padded). If you decide to cast, cast to VARCHAR or VARCHAR2:

 1 select data
 2   from V
 3  where translate(lower(data),
 4                  '0123456789abcdefghijklmnopqrstuvwxyz',
 5                  rpad('a',36,'a')) = rpad('a',length(data),'a')

SQL Server

Because SQL Server does not support a TRANSLATE function, you must walk each row and find any that contains a character that contains a non-alphanumeric value. That can be done many ways, but the following solution uses an ASCII-value evaluation:

  1 select data
  2   from (
  3 select v.data, iter.pos,
  4        substring(v.data,iter.pos,1) c,
  5        ascii(substring(v.data,iter.pos,1)) val
  6   from v,
  7        ( select id as pos from t100 ) iter
  8  where iter.pos <= len(v.data)
  9        ) x
 10  group by data
 11  having min(val) between 48 and 122

Discussion

The key to these solutions is being able to reference multiple characters concurrently. By using the function TRANSLATE you can easily manipulate all numbers or all characters without having to "iterate" and inspect each character one by one.

DB2, Oracle, and PostgreSQL

Only 9 of the 14 rows from view V are alphanumeric. To find the rows that are alphanumeric only, simply use the function TRANSLATE. In this example, TRANSLATE converts characters 09 and az to "a". Once the conversion is done, the converted row is then compared with a string of all "a" with the same length (as the row). If the length is the same, then you know all the characters are alphanumeric and nothing else.

By using the TRANSLATE function (using the Oracle syntax):

 where translate(lower(data),
                 '0123456789abcdefghijklmnopqrstuvwxyz',
                  rpad('a',36,'a'))

you convert all numbers and letters into a distinct character (I chose "a"). Once the data is converted, all strings that are indeed alphanumeric can be identified as a string comprising only a single character (in this case, "a"). This can be seen by running TRANSLATE by itself:

 
 select data, translate(lower(data),
                   '0123456789abcdefghijklmnopqrstuvwxyz',
                    rpad('a',36,'a'))
   from V

 DATA                 TRANSLATE(LOWER(DATA)
 -------------------- ---------------------
 CLARK                aaaaa
 …
 SMITH, $800.00       aaaaa, $aaa.aa
 …
 ALLEN30              aaaaaaa
 …

The alphanumeric values are converted, but the string lengths have not been modified. Because the lengths are the same, the rows to keep are the ones for which the call to TRANSLATE returns all a's. You keep those rows, rejecting the others, by comparing each original string's length with the length of its corresponding string of a's:

 
 select data, translate(lower(data),
                   '0123456789abcdefghijklmnopqrstuvwxyz',
                    rpad('a',36,'a')) translated,
         rpad('a',length(data),'a') fixed
   from V

 DATA                 TRANSLATED           FIXED
 -------------------- -------------------- ----------------
 CLARK                aaaaa                aaaaa
 …
 SMITH, $800.00       aaaaa, $aaa.aa       aaaaaaaaaaaaaa
 …
 ALLEN30              aaaaaaa              aaaaaaa
 …

The last step is to keep only the strings where TRANSLATED equals FIXED.

MySQL

The expression in the WHERE clause:

 where data regexp '[^0-9a-zA-Z]' = 0

causes rows that have only numbers or characters to be returned. The value ranges in the brackets, "0-9a-zA-Z", represent all possible numbers and letters. The character "^" is for negation, so the expression can be stated as "not numbers or letters." A return value of 1 is true and 0 is false, so the whole expression can be stated as "return rows where anything other than numbers and letters is false."

SQL Server

The first step is to walk each row returned by view V. Each character in the value returned for DATA will itself be returned as a row. The values returned by C represent each individual character for the values returned by DATA:

 +-----------------+------+------+------+
 | data            | pos  | c    | val  |
 +-----------------+------+------+------+
 | ADAMS, $1100.00 |    1 | A    |   65 |
 | ADAMS, $1100.00 |    2 | D    |   68 |
 | ADAMS, $1100.00 |    3 | A    |   65 |
 | ADAMS, $1100.00 |    4 | M    |   77 |
 | ADAMS, $1100.00 |    5 | S    |   83 |
 | ADAMS, $1100.00 |    6 | ,    |   44 |
 | ADAMS, $1100.00 |    7 |      |   32 |
 | ADAMS, $1100.00 |    8 | $    |   36 |
 | ADAMS, $1100.00 |    9 | 1    |   49 |
 | ADAMS, $1100.00 |   10 | 1    |   49 |
 | ADAMS, $1100.00 |   11 | 0    |   48 |
 | ADAMS, $1100.00 |   12 | 0    |   48 |
 | ADAMS, $1100.00 |   13 | .    |   46 |
 | ADAMS, $1100.00 |   14 | 0    |   48 |
 | ADAMS, $1100.00 |   15 | 0    |   48 |

Inline view Z not only returns each character in the column DATA row by row, it also provides the ASCII value for each character. For this particular implementation of SQL Server, the range 48122 represents alphanumeric characters. With that knowledge, you can group each row in DATA and filter out any such that the minimum ASCII value is not in the 48122 range.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server : Separating Numeric and Character Data

2008-06-13T01:25:00.000-07:00

Problem

You have (unfortunately) stored numeric data along with character data together in one column. You want to separate the character data from the numeric data. Consider the following result set:

 DATA
 ---------------
 SMITH800
 ALLEN1600
 WARD1250
 JONES2975
 MARTIN1250
 BLAKE2850
 CLARK2450
 SCOTT3000
 KING5000
 TURNER1500
 ADAMS1100
 JAMES950
 FORD3000
 MILLER1300

You would like the result to be:

 ENAME             SAL
 ---------- ----------
 SMITH             800
 ALLEN            1600
 WARD             1250
 JONES            2975
 MARTIN           1250
 BLAKE            2850
 CLARK            2450
 SCOTT            3000
 KING             5000
 TURNER           1500
 ADAMS            1100
 JAMES             950
 FORD             3000
 MILLER           1300

Solution

Use the built-in functions TRANSLATE and REPLACE to isolate the character from the numeric data. Like other recipes in this chapter, the trick is to use TRANSLATE to transform multiple characters into a single character you can reference. This way you are no longer searching for multiple numbers or characters, rather one character to represent all numbers or one character to represent all characters.

DB2

Use the functions TRANSLATE and REPLACE to isolate and separate the numeric from the character data:

  1 select replace(
  2      translate(data,'0000000000','0123456789'),'0','') ename,
  3        cast(
  4      replace(
  5    translate(lower(data),repeat('z',26),
  6           'abcdefghijklmnopqrstuvwxyz'),'z','') as integer) sal
  7    from (
  8  select ename||cast(sal as char(4)) data
  9    from emp
 10         ) x

Oracle

Use the functions TRANSLATE and REPLACE to isolate and separate the numeric from the character data:

  1 select replace(
  2      translate(data,'0123456789','0000000000'),'0') ename,
  3      to_number(
  5        replace(
  6        translate(lower(data),
  7                  'abcdefghijklmnopqrstuvwxyz',
  8                   rpad('z',26,'z')),'z')) sal
  9   from (
 10 select ename||sal data
 11   from emp
 12        )

PostgreSQL

Use the functions TRANSLATE and REPLACE to isolate and separate the numeric from the character data:

  1 select replace(
  2      translate(data,'0123456789','0000000000'),'0','') as ename,
  3           cast(
  4        replace(
  5      translate(lower(data),
  6                'abcdefghijklmnopqrstuvwxyz',
  7                rpad('z',26,'z')),'z','') as integer) as sal
  8   from (
  9 select ename||sal as data
 10   from emp
 11        ) x

Discussion

The syntax is a bit different for each DBMS, but the technique is the same. I will use the solution for Oracle in the discussion section. The key to solving this problem is to isolate the numeric and character data. You can use TRANSLATE and REPLACE to do this. To extract the numeric data, first isolate all character data using TRANSLATE:

 
 select data,
        translate(lower(data),
                 'abcdefghijklmnopqrstuvwxyz',
                 rpad('z',26,'z')) sal
   from (select ename||sal data from emp)

 DATA                 SAL
 -------------------- -------------------
 SMITH800             zzzzz800
 ALLEN1600            zzzzz1600
 WARD1250             zzzz1250
 JONES2975            zzzzz2975
 MARTIN1250           zzzzzz1250
 BLAKE2850            zzzzz2850
 CLARK2450            zzzzz2450
 SCOTT3000            zzzzz3000
 KING5000             zzzz5000
 TURNER1500           zzzzzz1500
 ADAMS1100            zzzzz1100
 JAMES950             zzzzz950
 FORD3000             zzzz3000
 MILLER1300           zzzzzz1300

By using TRANSLATE you convert every non-numeric character into a lowercase Z. The next step is to remove all instances of lowercase Z from each record using REPLACE, leaving only numerical characters that can then be cast to a number:

 
 select data,
        to_number(
          replace(
        translate(lower(data),
                  'abcdefghijklmnopqrstuvwxyz',
                  rpad('z',26,'z')),'z')) sal
   from (select ename||sal data from emp)

  DATA                        SAL
  -------------------- ----------
  SMITH800                    800
  ALLEN1600                  1600
  WARD1250                   1250
  JONES2975                  2975
  MARTIN1250                 1250
  BLAKE2850                  2850
  CLARK2450                  2450
  SCOTT3000                  3000
  KING5000                   5000
  TURNER1500                 1500
  ADAMS1100                  1100
  JAMES950                    950
  FORD3000                   3000
  MILLER1300                 1300

To extract the non-numeric characters, isolate the numeric characters using TRANSLATE:

 
 select data,
        translate(data,'0123456789','0000000000') ename
   from (select ename||sal data from emp)

  DATA                 ENAME
  -------------------- ----------
  SMITH800             SMITH000
  ALLEN1600            ALLEN0000
  WARD1250             WARD0000
  JONES2975            JONES0000
  MARTIN1250           MARTIN0000
  BLAKE2850            BLAKE0000
  CLARK2450            CLARK0000
  SCOTT3000            SCOTT0000
  KING5000             KING0000
  TURNER1500           TURNER0000
  ADAMS1100            ADAMS0000
  JAMES950             JAMES000
  FORD3000             FORD0000
  MILLER1300           MILLER0000

By using TRANSLATE you convert every numeric character into a zero. The next step is to remove all instances of zero from each record using REPLACE, leaving only non-numeric characters:

 
 select data,
        replace(translate(data,'0123456789','0000000000'),'0') ename
   from (select ename||sal data from emp)

  DATA                 ENAME
  -------------------- -------
  SMITH800             SMITH
  ALLEN1600            ALLEN
  WARD1250             WARD
  JONES2975            JONES
  MARTIN1250           MARTIN
  BLAKE2850            BLAKE
  CLARK2450            CLARK
  SCOTT3000            SCOTT
  KING5000             KING
  TURNER1500           TURNER
  ADAMS1100            ADAMS
  JAMES950             JAMES
  FORD3000             FORD
  MILLER1300           MILLER

Put the two techniques together and you have your solution.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL SERVER : Removing Unwanted Characters from a String

2008-06-12T06:56:00.002-07:00

Problem

You want to remove specific characters from your data. Consider this result set:

 ENAME             SAL
 ---------- ----------
 SMITH             800
 ALLEN            1600
 WARD             1250
 JONES            2975
 MARTIN           1250
 BLAKE            2850
 CLARK            2450
 SCOTT            3000
 KING             5000
 TURNER           1500
 ADAMS            1100
 JAMES             950
 FORD             3000
 MILLER           1300

You want to remove all zeros and vowels as shown by the following values in columns STRIPPED1 and STRIPPED2:

 ENAME      STRIPPED1         SAL STRIPPED2
 ---------- ---------- ---------- ---------
 SMITH      SMTH              800 8
 ALLEN      LLN              1600 16
 WARD       WRD              1250 125
 JONES      JNS              2975 2975
 MARTIN     MRTN             1250 125
 BLAKE      BLK              2850 285
 CLARK      CLRK             2450 245
 SCOTT      SCTT             3000 3
 KING       KNG              5000 5
 TURNER     TRNR             1500 15
 ADAMS      DMS              1100 11
 JAMES      JMS               950 95
 FORD       FRD              3000 3
 MILLER     MLLR             1300 13

Solution

Each DBMS provides functions for removing unwanted characters from a string. The functions REPLACE and TRANSLATE are most useful for this problem.

DB2

Use the built-in functions TRANSLATE and REPLACE to remove unwanted characters and strings:

 1 select ename,
 2        replace(translate(ename,'aaaaa','AEIOU'),'a','') stripped1,
 3        sal,
 4        replace(cast(sal as char(4)),'0','') stripped2
 5   from emp

MySQL and SQL Server

MySQL and SQL Server do not offer a TRANSLATE function, so several calls to REPLACE are needed:

  1 select ename,
  2        replace(
  3        replace(
  4        replace(
  5        replace(
  6        replace(ename,'A',''),'E',''),'I',''),'O',''),'U','')
  7        as stripped1,
  8        sal,
  9        replace(sal,0,'') stripped2
 10   from emp

Oracle and PostgreSQL

Use the built-in functions TRANSLATE and REPLACE to remove unwanted characters and strings:

 1 select ename,
 2        replace(translate(ename,'AEIOU','aaaaa'),'a')
 3        as stripped1,
 4        sal,
 5        replace(sal,0,'') as stripped2
 6   from emp

Discussion

The built-in function REPLACE removes all occurrences of zeros. To remove the vowels, use TRANSLATE to convert all vowels into one specific character (I used "a"; you can use any character), then use REPLACE to remove all occurrences of that character.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Counting the Occurrences of a Character in a String

2008-06-12T06:56:00.001-07:00

Problem

You want to count the number of times a character or substring occurs within a given string. Consider the following string:

 10,CLARK,MANAGER

You want to determine how many commas are in the string.

Solution

Subtract the length of the string without the commas from the original length of the string to determine the number of commas in the string. Each DBMS provides functions for obtaining the length of a string and removing characters from a string. In most cases, these functions are LENGTH and REPLACE, respectively (SQL Server users will use the built-in function LEN rather than LENGTH):

 1 select (length('10,CLARK,MANAGER')-
 2        length(replace('10,CLARK,MANAGER',',','')))/length(',')
 3        as cnt
 4   from t1

Discussion

You arrive at the solution by using simple subtraction. The call to LENGTH on line 1 returns the original size of the string, and the first call to LENGTH on line 2 returns the size of the string without the commas, which are removed by REPLACE.

By subtracting the two lengths you obtain the difference in terms of characters, which is the number of commas in the string. The last operation divides the difference by the length of your search string. This division is necessary if the string you are looking for has a length greater than 1. In the following example, counting the occurrence of "LL" in the string "HELLO HELLO" without dividing will return an incorrect result:

 
 select
        (length('HELLO HELLO')-
        length(replace('HELLO HELLO','LL','')))/length('LL')
        as correct_cnt,
        (length('HELLO HELLO')-
        length(replace('HELLO HELLO','LL',''))) as incorrect_cnt
   from t1

 CORRECT_CNT INCORRECT_CNT
 ----------- -------------
           2             4

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL : Embedding Quotes Within String Literals

2008-06-12T06:55:00.000-07:00

Problem

You want to embed quote marks within string literals. You would like to produce results such as the following with SQL:

 QMARKS
 --------------
 g'day mate
 beavers' teeth
 '

Solution

The following three SELECTs highlight different ways you can create quotes: in the middle of a string and by themselves:

 1 select 'g''day mate' qmarks from t1 union all
 2 select 'beavers'' teeth'    from t1 union all
 3 select ''''                 from t1

Discussion

When working with quotes, it's often useful to think of them like parentheses. When you have an opening parenthesis, you must always have a closing parenthesis. The same goes for quotes. Keep in mind that you should always have an even number of quotes across any given string. To embed a single quote within a string you need to use two quotes:

 
 select 'apples core', 'apple''s core',
         case when '' is null then 0 else 1 end
   from t1

  'APPLESCORE 'APPLE''SCOR CASEWHEN''ISNULLTHEN0ELSE1END
  ----------- ------------ -----------------------------
  apples core apple's core                             0

Following is the solution stripped down to its bare elements. You have two outer quotes defining a string literal, and, within that string literal you have two quotes that together represent just one quote in the string that you actually get:

 
 select '''' as quote from t1

 Q
 -
 '

When working with quotes, be sure to remember that a string literal comprising two quotes alone, with no intervening characters, is NULL.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Walking a String - SQL

2008-06-12T06:53:00.000-07:00

Problem

You want to traverse a string to return each character as a row, but SQL lacks a loop operation. For example, you want to display the ENAME "KING" from table EMP as four rows, where each row contains just characters from "KING".

Solution

Use a Cartesian product to generate the number of rows needed to return each character of a string on its own line. Then use your DBMS's built-in string parsing function to extract the characters you are interested in (SQL Server users will use SUBSTRING instead of SUBSTR):

 
 1 select substr(e.ename,iter.pos,1) as C
 2   from (select ename from emp where ename = 'KING') e,
 3        (select id as pos from t10) iter
 4  where iter.pos <= length(e.ename)
 
 C
 -
 K
 I
 N
 G

Discussion

The key to iterating through a string's characters is to join against a table that has enough rows to produce the required number of iterations. This example uses table T10, which contains 10 rows (it has one column, ID, holding the values 1 through 10). The maximum number of rows that can be returned from this query is 10.

The following example shows the Cartesian product between E and ITER (i.e., between the specific name and the 10 rows from T10) without parsing ENAME:

 
 select ename, iter.pos
   from (select ename from emp where ename = 'KING') e,
        (select id as pos from t10) iter
 
 ENAME             POS
 ---------- ----------
 KING                1
 KING                2
 KING                3
 KING                4
 KING                5
 KING                6
 KING                7
 KING                8
 KING                9
 KING               10

The cardinality of inline view E is 1, and the cardinality of inline view ITER is 10. The Cartesian product is then 10 rows. Generating such a product is the first step in mimicking a loop in SQL.

The solution uses a WHERE clause to break out of the loop after four rows have been returned. To restrict the result set to the same number of rows as there are characters in the name, that WHERE clause specifies ITER.POS <= LENGTH(E. ENAME) as the condition:

 
 select ename, iter.pos
   from (select ename from emp where ename = 'KING') e,
        (select id as pos from t10) iter
  where iter.pos <= length(e.ename)
 
 ENAME             POS
 ---------- ----------
 KING                1
 KING                2
 KING                3
 KING                4

Now that you have one row for each character in E.ENAME, you can use ITER.POS as a parameter to SUBSTR, allowing you to navigate through the characters in the string. ITER.POS increments with each row, and thus each row can be made to return a successive character from E.ENAME. This is how the solution example works.

Depending on what you are trying to accomplish you may or may not need to generate a row for every single character in a string. The following query is an example of walking E.ENAME and exposing different portions (more than a single character) of the string:

 select substr(e.ename,iter.pos) a,
        substr(e.ename,length(e.ename)-iter.pos+1) b
   from (select ename from emp where ename = 'KING') e,
        (select id pos from t10) iter
  where iter.pos <= length(e.ename)

 A          B
 ---------- ------
 KING       G
 ING        NG
 NG         ING
 G          KING

The most common scenarios for the recipes in this chapter involve walking the whole string to generate a row for each character in the string, or walking the string such that the number of rows generated reflects the number of particular characters or delimiters that are present in the string.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Describing the Data Dictionary Views in an Oracle Database

2008-06-11T07:45:00.000-07:00

Problem

You are using Oracle. You can't remember what data dictionary views are available to you, nor can you remember their column definitions. Worse yet, you do not have convenient access to vendor documentation.

Solution

This is an Oracle-specific recipe. Oracle not only maintains a robust set of data dictionary views, but there are even data dictionary views to document the data dictionary views. It's all so wonderfully circular.

Query the view named DICTIONARY to list data dictionary views and their purposes:

 select table_name, comments
   from dictionary
   order by table_name;

 TABLE_NAME                     COMMENTS
 ------------------------------ --------------------------------------------
 ALL_ALL_TABLES                 Description of all object and relational
                                tables accessible to the user

 ALL_APPLY                      Details about each apply process that
                                dequeues from the queue visible to the
                                current user
 …

Query DICT_COLUMNS to describe the columns in given a data dictionary view:

 select column_name, comments
      from dict_columns
  where table_name = 'ALL_TAB_COLUMNS';

 COLUMN_NAME                     COMMENTS
 ------------------------------- --------------------------------------------
 OWNER
 TABLE_NAME                      Table, view or cluster name
 COLUMN_NAME                     Column name
 DATA_TYPE                       Datatype of the column
 DATA_TYPE_MOD                   Datatype modifier of the column
 DATA_TYPE_OWNER                 Owner of the datatype of the column
 DATA_LENGTH                     Length of the column in bytes
 DATA_PRECISION                  Length: decimal digits (NUMBER) or binary
                                 digits (FLOAT)

Discussion

Back in the day when Oracle's documentation set wasn't so freely available on the Web, it was incredibly convenient that Oracle made the DICTIONARY and DICT_ COLUMNS views available. Knowing just those two views, you could bootstrap to learning about all the other views, and from thence to learning about your entire database.

Even today, it's convenient to know about DICTIONARY and DICT_COLUMNS. Often, if you aren't quite certain which view describes a given object type, you can issue a wildcard query to find out. For example, to get a handle on what views might describe tables in your schema:

 select table_name, comments
   from dictionary
  where table_name LIKE '%TABLE%'
  order by table_name;

This query returns all data dictionary view names that include the term "TABLE". This approach takes advantage of Oracle's fairly consistent data dictionary view naming conventions. Views describing tables are all likely to contain "TABLE" in their name. (Sometimes, as in the case of ALL_TAB_COLUMNS, TABLE is abbreviated TAB.)

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Using SQL to Generate SQL

2008-06-11T07:44:00.000-07:00

Problem

You want to create dynamic SQL statements, perhaps to automate maintenance tasks. You want to accomplish three tasks in particular: count the number of rows in your tables, disable foreign key constraints defined on your tables, and generate insert scripts from the data in your tables.

Solution

The concept is to use strings to build SQL statements, and the values that need to be filled in (such as the object name the command acts upon) will be supplied by data from the tables you are selecting from. Keep in mind, the queries only generate the statements; you must then run these statements via script, manually, or however you execute your SQL statements. The examples below are queries that would work on an Oracle system. For other RDBMSs the technique is exactly the same, the only difference being things like the names of the data dictionary tables and date formatting. The output shown from the queries below are a portion of the rows returned from an instance of Oracle on my laptop. Your result sets will of course vary.

 /* generate SQL to count all the rows in all your tables */

 
 select 'select count(*) from '||table_name||';' cnts
   from user_tables;

 CNTS
 ----------------------------------------
 select count(*) from ANT;
 select count(*) from BONUS;
 select count(*) from DEMO1;
 select count(*) from DEMO2;
 select count(*) from DEPT;
 select count(*) from DUMMY;
 select count(*) from EMP;
 select count(*) from EMP_SALES;
 select count(*) from EMP_SCORE;
 select count(*) from PROFESSOR;
 select count(*) from T;
 select count(*) from T1;
 select count(*) from T2;
 select count(*) from T3;
 select count(*) from TEACH;
 select count(*) from TEST;
 select count(*) from TRX_LOG;
 select count(*) from X;

 /* disable foreign keys from all tables */

 
 select 'alter table '||table_name||
        ' disable constraint '||constraint_name||';' cons
   from user_constraints
  where constraint_type = 'R';

 CONS
 ------------------------------------------------
 alter table ANT disable constraint ANT_FK;
 alter table BONUS disable constraint BONUS_FK;
 alter table DEMO1 disable constraint DEMO1_FK;
 alter table DEMO2 disable constraint DEMO2_FK;
 alter table DEPT disable constraint DEPT_FK;
 alter table DUMMY disable constraint DUMMY_FK;
 alter table EMP disable constraint EMP_FK;
 alter table EMP_SALES disable constraint EMP_SALES_FK;
 alter table EMP_SCORE disable constraint EMP_SCORE_FK;
 alter table PROFESSOR disable constraint PROFESSOR_FK;
 /* generate an insert script from some columns in table EMP */

 
 select 'insert into emp(empno,ename,hiredate) '||chr(10)||
        'values( '||empno||','||''''||ename
        ||''',to_date('||''''||hiredate||''') );' inserts
   from emp
  where deptno = 10;

 INSERTS
 --------------------------------------------------
 insert into emp(empno,ename,hiredate)
 values( 7782,'CLARK',to_date('09-JUN-1981 00:00:00') );

 insert into emp(empno,ename,hiredate)
 values( 7839,'KING',to_date('17-NOV-1981 00:00:00') );

 insert into emp(empno,ename,hiredate)
 values( 7934,'MILLER',to_date('23-JAN-1982 00:00:00') );

Discussion

Using SQL to generate SQL is particularly useful for creating portable scripts such as you might use when testing on multiple environments. Additionally, as can be seen by the examples above, using SQL to generate SQL is useful for performing batch maintenance, and for easily finding out information about multiple objects in one go. Generating SQL with SQL is an extremely simple operation, and the more you experiment with it the easier it will become. The examples provided should give you a nice base on how to build your own "dynamic" SQL scripts because, quite frankly, there's not much to it. Work on it and you'll get it.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL : Listing Foreign Keys Without Corresponding Indexes

2008-06-11T07:42:00.001-07:00

Problem

You want to list tables that have foreign key columns that are not indexed. For example, you want to determine if the foreign keys on table EMP are indexed.

Solution

DB2

Query SYSCAT.TABCONST, SYSCAT.KEYCOLUSE, SYSCAT.INDEXES, and SYSCAT.INDEXCOLUSE:

  1  select fkeys.tabname,
  2         fkeys.constname,
  3         fkeys.colname,
  4         ind_cols.indname
  5    from (
  6  select a.tabschema, a.tabname, a.constname, b.colname
  7    from syscat.tabconst a,
  8         syscat.keycoluse b
  9  where a.tabname    = 'EMP'
 10    and a.tabschema  = 'SMEAGOL'
 11    and a.type       = 'F'
 12    and a.tabname    = b.tabname
 13    and a.tabschema  = b.tabschema
 14        ) fkeys
 15        left join
 16        (
 17  select a.tabschema,
 18         a.tabname,
 19         a.indname,
 20         b.colname
 21    from syscat.indexes a,
 22         syscat.indexcoluse b
 23  where a.indschema  = b.indschema
 24    and a.indname    = b.indname
 25        ) ind_cols
 26     on (     fkeys.tabschema = ind_cols.tabschema
 27          and fkeys.tabname   = ind_cols.tabname
 28          and fkeys.colname   = ind_cols.colname )
 29  where ind_cols.indname is null

Oracle

Query SYS.ALL_CONS_COLUMNS, SYS.ALL_CONSTRAINTS, and SYS.ALL_ IND_COLUMNS:

  1  select a.table_name,
  2         a.constraint_name,
  3         a.column_name,
  4         c.index_name
  5    from all_cons_columns a,
  6         all_constraints b,
  7         all_ind_columns c
  8  where a.table_name      = 'EMP'
  9    and a.owner           = 'SMEAGOL'
 10    and b.constraint_type = 'R'
 11    and a.owner           = b.owner
 12    and a.table_name      = b.table_name
 13    and a.constraint_name = b.constraint_name
 14    and a.owner           = c.table_owner (+)
 15    and a.table_name      = c.table_name (+)
 16    and a.column_name     = c.column_name (+)
 17    and c.index_name      is null

PostgreSQL

Query INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_ SCHEMA.REFERENTIAL_CONSTRAINTS, INFORMATION_SCHEMA.COL-UMNS, and PG_CATALOG.PG_INDEXES:

  1  select fkeys.table_name,
  2         fkeys.constraint_name,
  3         fkeys.column_name,
  4         ind_cols.indexname
  5    from (
  6  select a.constraint_schema,
  7         a.table_name,
  8         a.constraint_name,
  9         a.column_name
 10    from information_schema.key_column_usage a,
 11         information_schema.referential_constraints b
 12   where a.constraint_name   = b.constraint_name
 13     and a.constraint_schema = b.constraint_schema
 14     and a.constraint_schema = 'SMEAGOL'
 15     and a.table_name        = 'EMP'
 16         ) fkeys
 17         left join
 18         (
 19  select a.schemaname, a.tablename, a.indexname, b.column_name
 20    from pg_catalog.pg_indexes a,
 21         information_schema.columns b
 22   where a.tablename  = b.table_name
 23     and a.schemaname = b.table_schema
 24         ) ind_cols
 25      on (  fkeys.constraint_schema = ind_cols.schemaname
 26           and fkeys.table_name     = ind_cols.tablename
 27           and fkeys.column_name    = ind_cols.column_name )
 28   where ind_cols.indexname is null

MySQL

You can use the SHOW INDEX command to retrieve index information such as index name, columns in the index, and ordinal position of the columns in the index. Additionally, you can query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to list the foreign keys for a given table. In MySQL 5, foreign keys are said to be indexed automatically, but can in fact be dropped. To determine whether a foreign key column's index has been dropped you can execute SHOW INDEX for a particular table and compare the output with that of INFORMATION_SCHEMA.KEY_ COLUMN_USAGE.COLUMN_NAME for the same table. If the COLUMN_NAME is listed in KEY_COLUMN_USAGE but is not returned by SHOW INDEX, you know that column is not indexed.

SQL Server

Query SYS.TABLES, SYS.FOREIGN_KEYS, SYS.COLUMNS, SYS.INDEXES, and SYS.INDEX_COLUMNS:

  1  select fkeys.table_name,
  2         fkeys.constraint_name,
  3         fkeys.column_name,
  4         ind_cols.index_name
  5    from (
  6  select a.object_id,
  7         d.column_id,
  8         a.name table_name,
  9         b.name constraint_name,
 10         d.name column_name
 11    from sys.tables a
 12         join
 13         sys.foreign_keys b
 14      on ( a.name          = 'EMP'
 15           and a.object_id = b.parent_object_id
 16         )
 17         join
 18         sys.foreign_key_columns c
 19     on (  b.object_id = c.constraint_object_id )
 20        join
 21        sys.columns d
 22     on (    c.constraint_column_id = d.column_id
 23         and a.object_id            = d.object_id
 24        )
 25        ) fkeys
 26        left join
 27        (
 28 select a.name index_name,
 29        b.object_id,
 30        b.column_id
 31   from sys.indexes a,
 32        sys.index_columns b
 33  where a.index_id = b.index_id
 34        ) ind_cols
 35     on (     fkeys.object_id = ind_cols.object_id
 36          and fkeys.column_id = ind_cols.column_id )
 37  where ind_cols.index_name is null

Discussion

Each vendor uses its own locking mechanism when modifying rows. In cases where there is a parent-child relationship enforced via foreign key, having indexes on the child column(s) can reducing locking (see your specific RDBMS documentation for details). In other cases, it is common that a child table is joined to a parent table on the foreign key column, so an index may help improve performance in that scenario as well.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL : Listing Constraints on a Table

2008-06-09T22:08:00.000-07:00

Problem

You want to list the constraints defined for a table in some schema and the columns they are defined on. For example, you want to find the constraints and the columns they are on for table EMP.

Solution

DB2

Query SYSCAT.TABCONST and SYSCAT.COLUMNS:

 1  select a.tabname, a.constname, b.colname, a.type
 2    from syscat.tabconst a,
 3         syscat.columns b
 4  where a.tabname   = 'EMP'
 5    and a.tabschema = 'SMEAGOL'
 6    and a.tabname   = b.tabname
 7    and a.tabschema = b.tabschema

Oracle

Query SYS.ALL_CONSTRAINTS and SYS.ALL_CONS_COLUMNS:

  1  select a.table_name,
  2         a.constraint_name,
  3         b.column_name,
  4         a.constraint_type
  5    from all_constraints a,
  6         all_cons_columns b
  7  where a.table_name      = 'EMP'
  8    and a.owner           = 'SMEAGOL'
  9    and a.table_name      = b.table_name
 10    and a.owner           = b.owner
 11    and a.constraint_name = b.constraint_name

PostgreSQL, MySQL, and SQL Server

Query INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_ SCHEMA.KEY_COLUMN_USAGE:

  1  select a.table_name,
  2         a.constraint_name,
  3         b.column_name,
  4         a.constraint_type
  5    from information_schema.table_constraints a,
  6         information_schema.key_column_usage b
  7  where a.table_name      = 'EMP'
  8    and a.table_schema    = 'SMEAGOL'
  9    and a.table_name      = b.table_name
 10    and a.table_schema    = b.table_schema
 11    and a.constraint_name = b.constraint_name

Discussion

Constraints are such a critical part of relational databases that it should go without saying why you need to know what constraints are on your tables. Listing the constraints on tables is useful for a variety of reasons: you may want to find tables missing a primary key, you may want to find which columns should be foreign keys but are not (i.e., child tables have data different from the parent tables and you want to know how that happened), or you may want to know about check constraints (Are columns nullable? Do they have to satisfy a specific condition? etc.).

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server : Listing Indexed Columns for a Table

2008-06-09T22:07:00.000-07:00

Problem

You want list indexes, their columns, and the column position (if available) in the index for a given table.

Solution

The vendor-specific solutions that follow all assume that you are listing indexes for the table EMP in the SMEAGOL schema.

DB2

Query SYSCAT.INDEXES:

 1  select a.tabname, b.indname, b.colname, b.colseq
 2    from syscat.indexes a,
 3         syscat.indexcoluse b
 3   where a.tabname   = 'EMP'
 4     and a.tabschema = 'SMEAGOL'
 5     and a.indschema = b.indschema
 6     and a.indname   = b.indname

Oracle

Query SYS.ALL_IND_COLUMNS:

 select table_name, index_name, column_name, column_position
   from sys.all_ind_columns
  where table_name  = 'EMP'
    and table_owner = 'SMEAGOL'

PostgreSQL

Query PG_CATALOG.PG_INDEXES and INFORMATION_SCHEMA.COLUMNS:

 1  select a.tablename,a.indexname,b.column_name
 2    from pg_catalog.pg_indexes a,
 3         information_schema.columns b
 4   where a.schemaname = 'SMEAGOL'
 5     and a.tablename  = b.table_name

MySQL

Use the SHOW INDEX command:

 show index from emp

SQL Server

Query SYS.TABLES, SYS.INDEXES, SYS.INDEX_COLUMNS, and SYS.COLUMNS:

  1  select a.name table_name,
  2         b.name index_name,
  3         d.name column_name,
  4         c.index_column_id
  5    from sys.tables a,
  6         sys.indexes b,
  7         sys.index_columns c,
  8         sys.columns d
  9  where a.object_id = b.object_id
 10    and b.object_id = c.object_id
 11    and b.index_id  = c.index_id
 12    and c.object_id = d.object_id
 13    and c.column_id = d.column_id
 14    and a.name      = 'EMP'

Discussion

When it comes to queries, it's important to know what columns are/aren't indexed. Indexes can provide good performance for queries against columns that are frequently used in filters and that are fairly selective. Indexes are also useful when joining between tables. By knowing what columns are indexed, you are already one step ahead of performance problems if they should occur. Additionally, you might want to find information about the indexes themselves: how many levels deep they are, how many distinct keys, how many leaf blocks, and so forth. Such information is also available from the views/tables queried in this recipe's solutions.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL ; Listing a Table's Columns

2008-06-09T22:06:00.000-07:00

Problem

You want to list the columns in a table, along with their data types, and their position in the table they are in.

Solution

The following solutions assume that you wish to list columns, their data types, and their numeric position in the table named EMP in the schema SMEAGOL.

DB2

Query SYSCAT.COLUMNS:

 
 1 select colname, typename, colno
 2   from syscat.columns
 3  where tabname   = 'EMP'
 4    and tabschema = 'SMEAGOL'

Oracle

Query ALL_TAB_COLUMNS:

 1 select column_name, data_type, column_id
 2   from all_tab_columns
 3  where owner      = 'SMEAGOL'
 4    and table_name = 'EMP'

PostgreSQL, MySQL, and SQL Server

Query INFORMATION_SCHEMA.COLUMNS:

 1 select column_name, data_type, ordinal_position
 2   from information_schema.columns
 3  where table_schema = 'SMEAGOL'
 4    and table_name   = 'EMP'

Discussion

Each vendor provides ways for you to get detailed information about your column data. In the examples above only the column name, data type, and position are returned. Additional useful items of information include length, nullability, and default values.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Listing Tables in a Schema - SQL

2008-06-09T22:00:00.000-07:00

Problem

You want to see a list all the tables you've created in a given schema.

Solution

The solutions that follow all assume you are working with the SMEAGOL schema. The basic approach to a solution is the same for all RDBMSs: you query a system table (or view) containing a row for each table in the database.

DB2

Query SYSCAT.TABLES:

 1 select tabname
 2   from syscat.tables
 3  where tabschema = 'SMEAGOL'

Oracle

Query SYS.ALL_TABLES:

 select table_name
   from all_tables
  where owner = 'SMEAGOL'

PostgreSQL, MySQL, and SQL Server

Query INFORMATION_SCHEMA.TABLES:

 1 select table_name
 2   from information_schema.tables
 3  where table_schema = 'SMEAGOL'

Discussion

In a delightfully circular manner, databases expose information about themselves through the very mechanisms that you create for your own applications: tables and views. Oracle, for example, maintains an extensive catalog of system views, such as ALL_TABLES, that you can query for information about tables, indexes, grants, and any other database object.

TIPS :

Oracle's catalog views are just that, views. They are based on an underlying set of tables that contain the information in a very user-unfriendly form. The views put a very usable face on Oracle's catalog data.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Deleting Records Referenced from Another Table - SQL Server

2008-06-09T06:19:00.001-07:00

Problem

You want to delete records from one table when those records are referenced from some other table. Consider the following table, named DEPT_ACCIDENTS, which contains one row for each accident that occurs in a manufacturing business. Each row records the department in which an accident occurred and also the type of accident.

 
 create table dept_accidents
 ( deptno         integer,
   accident_name  varchar(20) )

 insert into dept_accidents values (10,'BROKEN FOOT')
 insert into dept_accidents values (10,'FLESH WOUND')
 insert into dept_accidents values (20,'FIRE')
 insert into dept_accidents values (20,'FIRE')
 insert into dept_accidents values (20,'FLOOD')
 insert into dept_accidents values (30,'BRUISED GLUTE')

 select * from dept_accidents

     DEPTNO ACCIDENT_NAME
 ---------- --------------------
         10 BROKEN FOOT
         10 FLESH WOUND
         20 FIRE
         20 FIRE
         20 FLOOD
         30 BRUISED GLUTE

You want to delete from EMP the records for those employees working at a department that has three or more accidents.

Solution

Use a subquery and the aggregate function COUNT to find the departments with three or more accidents. Then delete all employees working in those departments:

 1 delete from emp
 2  where deptno in ( select deptno
 3                      from dept_accidents
 4                     group by deptno
 5                    having count(*) >= 3 )

Discussion

The subquery will identify which departments have three or more accidents:

 
 select deptno
   from dept_accidents
  group by deptno
 having count(*) >= 3

     DEPTNO
 ----------
         20

The DELETE will then delete any employees in the departments returned by the subquery (in this case, only in department 20).

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Deleting Duplicate Records : SQL Server

2008-06-09T06:18:00.000-07:00

Problem

You want to delete duplicate records from a table. Consider the following table:

 
 create table dupes (id integer, name varchar(10))
 insert into dupes values (1, 'NAPOLEON')
 insert into dupes values (2, 'DYNAMITE')
 insert into dupes values (3, 'DYNAMITE')
 insert into dupes values (4, 'SHE SELLS')
 insert into dupes values (5, 'SEA SHELLS')
 insert into dupes values (6, 'SEA SHELLS')
 insert into dupes values (7, 'SEA SHELLS')

 
 select * from dupes order by 1

         ID NAME
 ---------- ----------
          1 NAPOLEON
          2 DYNAMITE
          3 DYNAMITE
          4 SHE SELLS
          5 SEA SHELLS
          6 SEA SHELLS
          7 SEA SHELLS

For each group of duplicate names, such as "SEA SHELLS", you wish to arbitrarily retain one ID and delete the rest. In the case of "SEA SHELLS" you don't care whether you delete 5 and 6, or 5 and 7, or 6 and 7, but in the end you want just one record for "SEA SHELLS".

Solution

Use a subquery with an aggregate function such as MIN to arbitrarily choose the ID to retain (in this case only the NAME with the smallest value for ID is not deleted):

 1  delete from dupes
 2   where id not in ( select min(id)
 3                        from dupes
 4                       group by name )

Discussion

The first thing to do when deleting duplicates is to define exactly what it means for two rows to be considered "duplicates" of each other. For my example in this recipe, the definition of "duplicate" is that two records contain the same value in their NAME column. Having that definition in place, you can look to some other column to discriminate among each set of duplicates, to identify those records to retain. It's best if this discriminating column (or columns) is a primary key. I used the ID column, which is a good choice because no two records have the same ID.

The key to the solution is that you group by the values that are duplicated (by NAME in this case), and then use an aggregate function to pick off just one key value to retain. The subquery in the "Solution" example will return the smallest ID for each NAME, which represents the row you will not delete:

 
 select min(id)
   from dupes
  group by name

     MIN(ID)
 -----------
           2
           1
           5
           4

The DELETE then deletes any ID in the table that is not returned by the subquery (in this case IDs 3, 6, and 7). If you are having trouble seeing how this works, run the subquery first and include the NAME in the SELECT list:

 
 select name, min(id)
   from dupes
  group by name

 NAME          MIN(ID)
 ---------- ----------
 DYNAMITE            2
 NAPOLEON            1
 SEA SHELLS          5
 SHE SELLS           4

The rows returned by the subquery represent those to be retained. The NOT IN predicate in the DELETE statement causes all other rows to be deleted.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Deleting Referential Integrity Violations - SQL Server

2008-06-09T06:17:00.000-07:00

Problem

You wish to delete records from a table when those records refer to nonexistent records in some other table. Example: some employees are assigned to departments that do not exist. You wish to delete those employees.

Solution

Use the NOT EXISTS predicate with a subquery to test the validity of department numbers:

 delete from emp
  where not exists (
    select * from dept
     where dept.deptno = emp.deptno
 )

Alternatively, you can write the query using a NOT IN predicate:

 delete from emp
 where deptno not in (select deptno from dept)

Discussion

Deleting is really all about selecting: the real work lies in writing WHERE clause conditions to correctly describe those records that you wish to delete.

The NOT EXISTS solution uses a correlated subquery to test for the existence of a record in DEPT having a DEPTNO matching that in a given EMP record. If such a record exists, then the EMP record is retained. Otherwise, it is deleted. Each EMP record is checked in this manner.

The IN solution uses a subquery to retrieve a list of valid department numbers. DEPTNOs from each EMP record are then checked against that list. When an EMP record is found with a DEPTNO not in the list, the EMP record is deleted.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Deleting a Single Record : SQL Server

2008-06-09T06:15:00.000-07:00

Problem

You wish to delete a single record from a table.

Solution

This is a special case of "Deleting Specific Records." The key is to ensure that your selection criterion is narrow enough to specify only the one record that you wish to delete. Often you will want to delete based on the primary key. For example, to delete employee CLARK (EMPNO 7782):

 delete from emp where empno = 7782

Discussion

Deleting is always about identifying the rows to be deleted, and the impact of a DELETE always comes down to its WHERE clause. Omit the WHERE clause and the scope of a DELETE is the entire table. By writing conditions in the WHERE clause, you can narrow the scope to a group of records, or to a single record. When deleting a single record, you should typically be identifying that record based on its primary key or on one of its unique keys.

TIPS :

If your deletion criterion is based on a primary or unique key, then you can be sure of deleting only one record. (This is because your RDBMS will not allow two rows to contain the same primary or unique key values.) Otherwise, you may want to check first, to be sure you aren't about to inadvertently delete more records than you intend.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL : Deleting Specific Records

2008-06-09T06:14:00.002-07:00

Problem

You wish to delete records meeting a specific criterion from a table.

Solution

Use the DELETE command with a WHERE clause specifying which rows to delete. For example, to delete all employees in department 10:

 delete from emp where deptno = 10

Discussion

By using a WHERE clause with the DELETE command, you can delete a subset of rows in a table rather than all the rows.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server : Deleting All Records from a Table

2008-06-09T06:14:00.001-07:00

Problem

You want to delete all the records from a table.

Solution

Use the DELETE command to delete records from a table. For example, to delete all records from EMP:

 delete from emp

Discussion

When using the DELETE command without a WHERE clause, you will delete all rows from the table specified.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server : Merging Records

2008-06-09T06:12:00.000-07:00

Problem

You want to conditionally insert, update, or delete records in a table depending on whether or not corresponding records exist. (If a record exists, then update; if not,then insert; if after updating a row fails to meet a certain condition, delete it.) For example, you want to modify table EMP_COMMISSION such that:

If any employee in EMP_COMMISSION also exists in table EMP, then update their commission (COMM) to 1000.
For all employees who will potentially have their COMM updated to 1000, if their SAL is less than 2000, delete them (they should not be exist in EMP_COMMISSION).
Otherwise, insert the EMPNO, ENAME, and DEPTNO values from table EMP into table EMP_COMMISSION.

Essentially, you wish to execute either an UPDATE or an INSERT depending on whether a given row from EMP has a match in EMP_COMMISSION. Then you wish to execute a DELETE if the result of an UPDATE causes a commission that's too high.

The following rows are currently in tables EMP and EMP_COMMISSION, respectively:

 
 select deptno,empno,ename,comm
   from emp
  order by 1

 DEPTNO      EMPNO ENAME        COMM
 ------ ---------- ------ ----------
     10       7782  CLARK
     10       7839  KING
     10       7934  MILLER
     20       7369  SMITH
     20       7876  ADAMS
     20       7902  FORD
     20       7788  SCOTT
     20       7566  JONES
     30       7499  ALLEN        300
     30       7698  BLAKE
     30       7654  MARTIN      1400
     30       7900  JAMES
     30       7844  TURNER         0
     30       7521  WARD         500

 
 select deptno,empno,ename,comm
   from emp_commission
  order by 1

     DEPTNO      EMPNO ENAME            COMM
 ---------- ---------- ---------- ----------
         10       7782 CLARK
         10       7839 KING
         10       7934 MILLER

Solution

Oracle is currently the only RDBMS with a statement designed to solve this problem. That statement is the MERGE statement, and it can perform either an UPDATE or an INSERT, as needed. For example:

 1  merge into emp_commission ec
 2  using (select * from emp) emp
 3     on (ec.empno=emp.empno)
 4   when matched then
 5        update set ec.comm = 1000
 6        delete where (sal <>
 7   when not matched then
 8        insert (ec.empno,ec.ename,ec.deptno,ec.comm)
 9        values (emp.empno,emp.ename,emp.deptno,emp.comm)

Discussion

The join on line 3 of the solution determines what rows already exist and will be updated. The join is between EMP_COMMISSION (aliased as EC) and the subquery (aliased as emp). When the join succeeds, the two rows are considered "matched" and the UPDATE specified in the WHEN MATCHED clause is executed. Otherwise, no match is found and the INSERT in WHEN NOT MATCHED is executed. Thus, rows from table EMP that do not have corresponding rows based on EMPNO in table EMP_COMMISSION will be inserted into EMP_COMMISSION. Of all the employees in table EMP only those in DEPTNO 10 should have their COMM updated in EMP_COMMISSION, while the rest of the employees are inserted. Additionally, since MILLER is in DEPTNO 10 he is a candidate to have his COMM updated, but because his SAL is less than 2000 it is deleted from EMP_COMMISSION.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Updating with Values from Another Table - SQL Server

2008-06-06T00:46:00.000-07:00

Problem

You wish to update rows in one table using values from another. For example, you have a table called NEW_SAL, which holds the new salaries for certain employees. The contents of table NEW_SAL are:

 
 select *
   from new_sal

 DEPTNO        SAL
 ------ ----------
     10       4000

Column DEPTNO is the primary key of table NEW_SAL. You want to update the salaries and commission of certain employees in table EMP using values table NEW_SAL if there is a match between EMP.DEPTNO and NEW_SAL.DEPTNO, update EMP.SAL to NEW_SAL.SAL, and update EMP.COMM to 50% of NEW_SAL.SAL. The rows in EMP are as follows:

 
 select deptno,ename,sal,comm
   from emp
  order by 1

 DEPTNO ENAME             SAL       COMM
 ------ ---------- ---------- ----------
     10 CLARK           2450            
     10 KING            5000
     10 MILLER          1300
     20 SMITH            800
     20 ADAMS           1100
     20 FORD            3000
     20 SCOTT           3000
     20 JONES           2975
     30 ALLEN           1600         300
     30 BLAKE           2850
     30 MARTIN          1250        1400
     30 JAMES            950
     30 TURNER          1500           0
     30 WARD            1250         500

Solution

Use a join between NEW_SAL and EMP to find and return the new COMM values to the UPDATE statement. It is quite common for updates such as this one to be performed via correlated subquery. Another technique involves creating a view (traditional or inline, depending on what your database supports), then updating that view.

DB2 and MySQL

Use a correlated subquery to set new SAL and COMM values in EMP. Also use a correlated subquery to identify which rows from EMP should be updated:

 1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2
 2                                      from new_sal ns
 3                                     where ns.deptno=e.deptno)
 4  where exists ( select null
 5                   from new_sal ns
 6                  where ns.deptno = e.deptno )

Oracle

The method for the DB2 solution will certainly work for Oracle, but as an alternative, you can update an inline view:

 1 update (
 2  select e.sal as emp_sal, e.comm as emp_comm,
 3         ns.sal as ns_sal, ns.sal/2 as ns_comm
 4    from emp e, new_sal ns
 5   where e.deptno = ns.deptno
 6 ) set emp_sal = ns_sal, emp_comm = ns_comm

PostgreSQL

The method used for the DB2 solution will work for PostgreSQL, but as an alternative you can (quite conveniently) join directly in the UPDATE statement:

 1 update emp
 2    set sal = ns.sal,
 3        comm = ns.sal/2
 4   from new_sal ns
 5  where ns.deptno = emp.deptno

SQL Server

The method used for the DB2 solution will work for SQL Server, but as an alternative you can (similarly to the PostgreSQL solution) join directly in the UPDATE statement:

 1 update e
 2    set e.sal  = ns.sal,
 3        e.comm = ns.sal/2
 4   from emp e,
 5        new_sal ns
 6  where ns.deptno = e.deptno

Discussion

Before discussing the different solutions, I'd like to mention something important regarding updates that use queries to supply new values. A WHERE clause in the subquery of a correlated update is not the same as the WHERE clause of the table being updated. If you look at the UPDATE statement in the "Problem" section, the join on DEPTNO between EMP and NEW_SAL is done and returns rows to the SET clause of the UPDATE statement. For employees in DEPTNO 10, valid values are returned because there is a match DEPTNO in table NEW_SAL. But what about employees in the other departments? NEW_SAL does not have any other departments, so the SAL and COMM for employees in DEPTNOs 20 and 30 are set to NULL. Unless you are doing so via LIMIT or TOP or whatever mechanism your vendor supplies for limiting the number of rows returned in a result set, the only way to restrict rows from a table in SQL is to use a WHERE clause. To correctly perform this UPDATE, use a WHERE clause on the table being updated along with a WHERE clause in the correlated subquery.

DB2 and MySQL

To ensure you do not update every row in table EMP, remember to include a correlated subquery in the WHERE clause of the UPDATE. Performing the join (the correlated subquery) in the SET clause is not enough. By using a WHERE clause in the UPDATE, you ensure that only rows in EMP that match on DEPTNO to table NEW_SAL are updated. This holds true for all RDBMSs.

Oracle

In the Oracle solution using the update join view, you are using equi-joins to determine which rows will be updated. You can confirm which rows are being updated by executing the query independently. To be able to successfully use this type of UPDATE, you must first understand the concept of key-preservation. The DEPTNO column of the table NEW_SAL is the primary key of that table, thus its values are unique within the table. When joining between EMP and NEW_SAL, however, NEW_SAL.DEPTNO is not unique in the result set, as can be seen below:

 
 select e.empno, e.deptno e_dept, ns.sal, ns.deptno ns_deptno
   from emp e, new_sal ns
  where e.deptno = ns.deptno

 EMPNO     E_DEPT        SAL  NS_DEPTNO
 ----- ---------- ---------- ----------
  7782         10       4000         10
  7839         10       4000         10
  7934         10       4000         10

To enable Oracle to update this join, one of the tables must be key-preserved, meaning that if its values are not unique in the result set, it should at least be unique in the table it comes from. In this case NEW_SAL has a primary key on DEPTNO, which makes it unique in the table. Because it is unique in its table, it may appear multiple times in the result set and will still be considered key-preserved, thus allowing the update to complete successfully.

PostgreSQL and SQL Server

The syntax is a bit different between these two platforms, but the technique is the same. Being able to join directly in the UPDATE statement is extremely convenient. Since you specify which table to update (the table listed after the UPDATE keyword) there's no confusion as to which table's rows are modified. Additionally, because you are using joins in the update (since there is an explicit WHERE clause), you can avoid some of the pitfalls when coding correlated subquery updates; in particular, if you missed a join here, it would be very obvious you'd have a problem.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Updating when Corresponding Rows Exist ::SQL Server::

2008-06-06T00:45:00.000-07:00

Problem

You want to update rows in one table when corresponding rows exist in another. For example, if an employee appears in table EMP_BONUS, you want to increase that employee's salary (in table EMP) by 20 percent. The following result set represents the data currently in table EMP_BONUS:

 
 select empno, ename
   from emp_bonus

      EMPNO ENAME
 ---------- ---------
       7369 SMITH
       7900 JAMES
       7934 MILLER

Solution

Use a subquery in your UPDATE statement's WHERE clause to find employees in table EMP that are also in table EMP_BONUS. Your UPDATE will then act only on those rows, enabling you to increase their salary by 20 percent:

 1 update emp
 2    set sal=sal*1.20
 3  where empno in ( select empno from emp_bonus )

Discussion

The results from the subquery represent the rows that will be updated in table EMP. The IN predicate tests values of EMPNO from the EMP table to see whether they are in the list of EMPNO values returned by the subquery. When they are, the corresponding SAL values are updated.

Alternatively, you can use EXISTS instead of IN:

 update emp
    set sal = sal*1.20
  where exists ( select null
                   from emp_bonus
                  where emp.empno=emp_bonus.empno )

You may be surprised to see NULL in the SELECT list of the EXISTS subquery. Fear not, that NULL does not have an adverse effect on the update. In my opinion it increases readability as it reinforces the fact that, unlike the solution using a subquery with an IN operator, what will drive the update (i.e., which rows will be updated) will be controlled by the WHERE clause of the subquery, not the values returned as a result of the subquery's SELECT list.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server : Modifying Records in a Table

2008-06-06T00:44:00.002-07:00

Problem

You want to modify values for some or all rows in a table. For example, you might want to increase the salaries of everyone in department 20 by 10%. The following result set shows the DEPTNO, ENAME, and SAL for employees in that department:

 
 select deptno,ename,sal
   from emp
  where deptno = 20
  order by 1,3

 DEPTNO ENAME             SAL
 ------ ---------- ----------
     20 SMITH             800
     20 ADAMS            1100
     20 JONES            2975
     20 SCOTT            3000
     20 FORD             3000

You want to bump all the SAL values by 10%.

Solution

Use the UPDATE statement to modify existing rows in a database table. For example:

 1 update emp
 2    set sal = sal*1.10
 3  where deptno = 20

Discussion

Use the UPDATE statement along with a WHERE clause to specify which rows to update; if you exclude a WHERE clause, then all rows are updated. The expression SAL*1.10 in this solution returns the salary increased by 10%.

When preparing for a mass update, you may wish to preview the results. You can do that by issuing a SELECT statement that includes the expressions you plan to put into your SET clauses. The following SELECT shows the result of a 10% salary increase:

 
 select deptno,
        ename,
        sal      as orig_sal,
        sal*.10  as amt_to_add,
        sal*1.10 as new_sal
   from emp
  where deptno=20
  order by 1,5

 DEPTNO ENAME  ORIG_SAL AMT_TO_ADD  NEW_SAL
 ------ ------ -------- ----------  -------
     20 SMITH       800         80      880
     20 ADAMS      1100        110     1210
     20 JONES      2975        298     3273
     20 SCOTT      3000        300     3300
     20 FORD       3000        300     3300

The salary increase is broken down into two columns: one to show the increase over the old salary, and the other to show the new salary.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Blocking Inserts to Certain Columns - Sql Server

2008-06-06T00:44:00.001-07:00

Problem

You wish to prevent users, or an errant software application, from inserting values into certain table columns. For example, you wish to allow a program to insert into EMP, but only into the EMPNO, ENAME, and JOB columns.

Solution

Create a view on the table exposing only those columns you wish to expose. Then force all inserts to go through that view.

For example, to create a view exposing the three columns in EMP:

 create view new_emps as
 select empno, ename, job
   from emp

Grant access to this view to those users and programs allowed to populate only the three fields in the view. Do not grant those users insert access to the EMP table. Users may then create new EMP records by inserting into the NEW_EMPS view, but they will not be able to provide values for columns other than the three that are specified in the view definition.

Discussion

When you insert into a simple view such as in the solution, your database server will translate that insert into the underlying table. For example, the following insert:

 insert into new_emps
    (empno ename, job)
    values (1, 'Jonathan', 'Editor')

will be translated behind the scenes into:

 insert into emp
    (empno ename, job)
    values (1, 'Jonathan', 'Editor')

It is also possible, but perhaps less useful, to insert into an inline view (currently only supported by Oracle):

 insert into
   (select empno, ename, job
      from emp)
 values (1, 'Jonathan', 'Editor')

View insertion is a complex topic. The rules become very complicated very quickly for all but the simplest of views. If you plan to make use of the ability to insert into views, it is imperative that you consult and fully understand your vendor documentation on the matter.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Inserting into Multiple Tables at Once SQL Server

2008-06-06T00:43:00.000-07:00

Problem

You want to take rows returned by a query and insert those rows into multiple target tables. For example, you want to insert rows from DEPT into tables DEPT_EAST, DEPT_WEST, and DEPT_MID. All three tables have the same structure (same columns and data types) as DEPT and are currently empty.

Solution

The solution is to insert the result of a query into the target tables. The difference from "Copying Rows from One Table into Another" is that for this problem you have multiple target tables.

Oracle

Use either the INSERT ALL or INSERT FIRST statement. Both share the same syntax except for the choice between the ALL and FIRST keywords. The following statement uses INSERT ALL to cause all possible target tables to be considered:

 1   insert all
 2     when loc in ('NEW YORK','BOSTON') then
 3       into dept_east (deptno,dname,loc) values (deptno,dname,loc)
 4     when loc = 'CHICAGO' then
 5       into dept_mid (deptno,dname,loc) values (deptno,dname,loc)
 6     else
 7       into dept_west (deptno,dname,loc) values (deptno,dname,loc)
 8     select deptno,dname,loc
 9       from dept

DB2

Insert into an inline view that performs a UNION ALL on the tables to be inserted. You must also be sure to place constraints on the tables that will ensure each row goes into the correct table:

 create table dept_east
 ( deptno integer,
   dname  varchar(10),
   loc    varchar(10) check (loc in ('NEW YORK','BOSTON')))

 create table dept_mid
 ( deptno integer,
   dname  varchar(10),
   loc    varchar(10) check (loc = 'CHICAGO'))

 create table dept_west
 ( deptno integer,
   dname  varchar(10),
   loc    varchar(10) check (loc = 'DALLAS'))

 1  insert into (
 2    select * from dept_west union all
 3    select * from dept_east union all
 4    select * from dept_mid
 5  ) select * from dept

MySQL, PostgreSQL, and SQL Server

As of the time of this writing, these vendors do not support multi-table inserts.

Discussion

Oracle

Oracle's multi-table insert uses WHEN-THEN-ELSE clauses to evaluate the rows from the nested SELECT and insert them accordingly. In this recipe's example, INSERT ALL and INSERT FIRST would produce the same result, but there is a difference between the two. INSERT FIRST will break out of the WHEN-THEN-ELSE evaluation as soon as it encounters a condition evaluating to true; INSERT ALL will evaluate all conditions even if prior tests evaluate to true. Thus, you can use INSERT ALL to insert the same row into more than one table.

DB2

My DB2 solution is a bit of a hack. It requires that the tables to be inserted into have constraints defined to ensure that each row evaluated from the subquery will go into the correct table. The technique is to insert into a view that is defined as the UNION ALL of the tables. If the check constraints are not unique amongst the tables in the INSERT (i.e., multiple tables have the same check constraint), the INSERT statement will not know where to put the rows and it will fail.

MySQL, PostgreSQL, and SQL Server

As of the time of this writing, only Oracle and DB2 currently provide mechanisms to insert rows returned by a query into one or more of several tables within the same statement.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Copying a Table Definition - SQL Server

2008-06-04T23:11:00.000-07:00

Problem

You want to create a new table having the same set of columns as an existing table. For example, you want to create a copy of the DEPT table and call it DEPT_2. You do not want to copy the rows, only the column structure of the table.

Solution

DB2

Use the LIKE clause with the CREATE TABLE command:

 create table dept_2 like dept

Oracle, MySQL, and PostgreSQL

Use the CREATE TABLE command with a subquery that returns no rows:

 1 create table dept_2
 2 as
 3 select *
 4   from dept
 5  where 1 = 0

SQL Server

Use the INTO clause with a subquery that returns no rows:

 1 select *
 2   into dept_2
 3   from dept
 4  where 1 = 0

Discussion

DB2

DB2's CREATE TABLE…LIKE command allows you to easily use one table as the pattern for creating another. Simply specify your pattern table's name following the LIKE keyword.

Oracle, MySQL, and PostgreSQL

When using Create Table As Select (CTAS), all rows from your query will be used to populate the new table you are creating unless you specify a false condition in the WHERE clause. In the solution provided, the expression "1 = 0" in the WHERE clause of the query causes no rows to be returned. Thus the result of the CTAS statement is an empty table based on the columns in the SELECT clause of the query.

SQL Server

When using INTO to copy a table, all rows from your query will be used to populate the new table you are creating unless you specify a false condition in the WHERE clause of your query. In the solution provided, the expression "1 = 0" in the predicate of the query causes no rows to be returned. The result is an empty table based on the columns in the SELECT clause of the query.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Copying Rows from One Table into Another SQL SERVER

2008-06-04T23:10:00.000-07:00

Problem

You want to copy rows from one table to another by using a query. The query may be complex or simple, but ultimately you want the result to be inserted into another table. For example, you want to copy rows from the DEPT table to the DEPT_EAST table. The DEPT_EAST table has already been created with the same structure (same columns and data types) as DEPT and is currently empty.

Solution

Use the INSERT statement followed by a query to produce the rows you want:

 1 insert into dept_east (deptno,dname,loc)
 2 select deptno,dname,loc
 3   from dept
 4  where loc in ( 'NEW YORK','BOSTON' )

Discussion

Simply follow the INSERT statement with a query that returns the desired rows. If you want to copy all rows from the source table, exclude the WHERE clause from the query. Like a regular insert, you do not have to explicitly specify which columns you are inserting into. But if you do not specify your target columns, you must insert into all of the table's columns, and you must be mindful of the order of the values in the SELECT list as described earlier in "Inserting a New Record."

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

SQL Server - Overriding a Default Value with NULL

2008-06-04T23:09:00.000-07:00

Problem

You are inserting into a column having a default value, and you wish to override that default value by setting the column to NULL. Consider the following table:

 create table D (id integer default 0, foo VARCHAR(10))

You wish to insert a row with a NULL value for ID.

Solution

You can explicitly specify NULL in your values list:

 insert into d (id, foo) values (null, 'Brighten')

Discussion

Not everyone realizes that you can explicitly specify NULL in the values list of an INSERT statement. Typically, when you do not wish to specify a value for a column, you leave that column out of your column and values lists:

 insert into d (foo) values ('Brighten')

Here, no value for ID is specified. Many would expect the column to taken on the null value, but, alas, a default value was specified at table creation time, so the result of the preceding INSERT is that ID takes on the value 0 (the default). By specifying NULL as the value for a column, you can set the column to NULL despite any default value.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Inserting Default Values - SQL Server

2008-06-04T23:08:00.000-07:00

Problem

A table can be defined to take default values for specific columns. You want to insert a row of default values without having to specify those values. Consider the following table:

 create table D (id integer default 0)

You want to insert zero without explicitly specifying zero in the values list of an INSERT statement. You want to explicitly insert the default, whatever that default is.

Solution

All brands support use of the DEFAULT keyword as a way of explicitly specifying the default value for a column. Some brands provide additional ways to solve the problem.

The following example illustrates the use of the DEFAULT keyword:

 insert into D values (default)

You may also explicitly specify the column name, which you'll need to do anytime you are not inserting into all columns of a table:

 insert into D (id) values (default)

Oracle8i Database and prior versions do not support the DEFAULT keyword. Prior to Oracle9i Database, there was no way to explicitly insert a default column value.

MySQL allows you to specify an empty values list if all columns have a default value defined:

 insert into D values ()

In this case, all columns will be set to their default values.

PostgreSQL and SQL Server support a DEFAULT VALUES clause:

 insert into D default values

The DEFAULT VALUES clause causes all columns to take on their default values.

Discussion

The DEFAULT keyword in the values list will insert the value that was specified as the default for a particular column during table creation. The keyword is available for all DBMSs.

MySQL, PostgreSQL, and SQL Server users have another option available if all columns in the table are defined with a default value (as table D is in this case). You may use an empty VALUES list (MySQL) or specify the DEFAULT VALUES clause (PostgreSQL and SQL Server) to create a new row with all default values; otherwise, you need to specify DEFAULT for each column in the table.

For tables with a mix of default and non-default columns, inserting default values for a column is as easy as excluding the column from the insert list; you do not need to use the DEFAULT keyword. Say that table D had an additional column that was not defined with a default value:

 create table D (id integer default 0, foo varchar(10))

You can insert a default for ID by listing only FOO in the insert list:

 insert into D (name) values ('Bar')

This statement will result in a row in which ID is 0 and FOO is "Bar". ID takes on its default value because no other value is specified.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Inserting a New Record - SQL Server

2008-06-04T23:06:00.000-07:00

Problem

You want to insert a new record into a table. For example, you want to insert a new record into the DEPT table. The value for DEPTNO should be 50, DNAME should be "PROGRAMMING", and LOC should be "BALTIMORE".

Solution

Use the INSERT statement with the VALUES clause to insert one row at a time:

 insert into dept (deptno,dname,loc)
 values (50,'PROGRAMMING','BALTIMORE')

For DB2 and MySQL you have the option of inserting one row at a time or multiple rows at a time by including multiple VALUES lists:

 /* multi row insert */
 insert into dept (deptno,dname,loc)
 values (1,'A','B'),
        (2,'B','C')

Discussion

The INSERT statement allows you to create new rows in database tables. The syntax for inserting a single row is consistent across all database brands.

As a shortcut, you can omit the column list in an INSERT statement:

 insert into dept
 values (50,'PROGRAMMING','BALTIMORE')

However, if you do not list your target columns, you must insert into all of the columns in the table, and be mindful of the order of the values in the VALUES list; you must supply values in the same order in which the database displays columns in response to a SELECT * query.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Using NULLs in Operations and Comparisons SQL

2008-06-04T03:18:00.000-07:00

Problem

NULL is never equal to or not equal to any value, not even itself, but you want to evaluate values returned by a nullable column like you would evaluate real values. For example, you want to find all employees in EMP whose commission (COMM) is less than the commission of employee "WARD". Employees with a NULL commission should be included as well.

Solution

Use a function such as COALESCE to transform the NULL value into a real value that can be used in standard evaluation:

 1 select ename,comm
 2   from emp
 3  where coalesce(comm,0) < ( select comm
 4                                  from emp
 5                                 where ename = 'WARD' )

Discussion

The COALESCE function will return the first non-NULL value from the list of values passed to it. When a NULL value is encountered it is replaced by zero, which is then compared with Ward's commission. This can be seen by putting the COALESCE function in the SELECT list:

 
 select ename,comm,coalesce(comm,0)
   from emp
  where coalesce(comm,0) < ( select comm
                                  from emp
                                 where ename = 'WARD' )

  ENAME            COMM COALESCE(COMM,0)
  ---------- ---------- ----------------
  SMITH                                0
  ALLEN             300              300
  JONES                                0
  BLAKE                                0
  CLARK                                0
  SCOTT                                0
  KING                                 0
  TURNER              0                0
  ADAMS                                0
  JAMES                                0
  FORD                                 0
  MILLER                               0

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Returning Missing Data from Multiple Tables : SQL Server

2008-06-04T03:16:00.000-07:00

Problem

You want to return missing data from multiple tables simultaneously. Returning rows from table DEPT that do not exist in table EMP (any departments that have no employees) requires an outer join. Consider the following query, which returns all DEPTNOs and DNAMEs from DEPT along with the names of all the employees in each department (if there is an employee in a particular department):

 
 select d.deptno,d.dname,e.ename
   from dept d left outer join emp e
     on (d.deptno=e.deptno)

    DEPTNO DNAME          ENAME
 --------- -------------- ----------
        20 RESEARCH       SMITH
        30 SALES          ALLEN
        30 SALES          WARD
        20 RESEARCH       JONES
        30 SALES          MARTIN
        30 SALES          BLAKE
        10 ACCOUNTING     CLARK
        20 RESEARCH       SCOTT
        10 ACCOUNTING     KING
        30 SALES          TURNER
        20 RESEARCH       ADAMS
        30 SALES          JAMES
        20 RESEARCH       FORD
        10 ACCOUNTING     MILLER
        40 OPERATIONS

The last row, the OPERATIONS department, is returned despite that department not having any employees, because table EMP was outer joined to table DEPT. Now, suppose there was an employee without a department. How would you return the above result set along with a row for the employee having no department? In other words, you want to outer join to both table EMP and table DEPT, and in the same query. After creating the new employee, a first attempt may look like this:

 insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
 select 1111,'YODA','JEDI',null,hiredate,sal,comm,null
   from emp
  where ename = 'KING'

 
 select d.deptno,d.dname,e.ename
   from dept d right outer join emp e
     on (d.deptno=e.deptno)

     DEPTNO DNAME        ENAME
 ---------- ------------ ----------
         10 ACCOUNTING   MILLER
         10 ACCOUNTING   KING
         10 ACCOUNTING   CLARK
         20 RESEARCH     FORD
         20 RESEARCH     ADAMS
         20 RESEARCH     SCOTT
         20 RESEARCH     JONES
         20 RESEARCH     SMITH
         30 SALES        JAMES
         30 SALES        TURNER
         30 SALES        BLAKE
         30 SALES        MARTIN
         30 SALES        WARD
         30 SALES        ALLEN
                         YODA

This outer join manages to return the new employee but lost the OPERATIONS department from the original result set. The final result set should return a row for YODA as well as OPERATIONS, such as the following:

     DEPTNO DNAME        ENAME
 ---------- ------------ --------
         10 ACCOUNTING   CLARK
         10 ACCOUNTING   KING
         10 ACCOUNTING   MILLER
         20 RESEARCH     ADAMS
         20 RESEARCH     FORD
         20 RESEARCH     JONES
         20 RESEARCH     SCOTT
         20 RESEARCH     SMITH
         30 SALES        ALLEN
         30 SALES        BLAKE
         30 SALES        JAMES
         30 SALES        MARTIN
         30 SALES        TURNER
         30 SALES        WARD
         40 OPERATIONS
                          YODA

Solution

Use a full outer join to return missing data from both tables based on a common value.

DB2, MySQL, PostgreSQL, SQL Server

Use the explicit FULL OUTER JOIN command to return missing rows from both tables along with matching rows:

 1 select d.deptno,d.dname,e.ename
 2   from dept d full outer join emp e
 3     on (d.deptno=e.deptno)

Alternatively, union the results of two different outer joins:

 1 select d.deptno,d.dname,e.ename
 2   from dept d right outer join emp e
 3     on (d.deptno=e.deptno)
 4  union
 5 select d.deptno,d.dname,e.ename
 6   from dept d left outer join emp e
 7     on (d.deptno=e.deptno)

Oracle

If you are on Oracle9i Database or later, you can use either of the preceding solutions. Alternatively, you can use Oracle's proprietary outer join syntax, which is the only choice for users on Oracle8i Database and earlier:

 1 select d.deptno,d.dname,e.ename   
 2   from dept d, emp e
 3  where d.deptno = e.deptno(+)
 4  union
 5 select d.deptno,d.dname,e.ename
 6   from dept d, emp e
 7  where d.deptno(+) = e.deptno

Discussion

The full outer join is simply the combination of outer joins on both tables. To see how a full outer join works "under the covers," simply run each outer join, then union the results. The following query returns rows from table DEPT and any matching rows from table EMP (if any).

 
 select d.deptno,d.dname,e.ename
   from dept d left outer join emp e
     on (d.deptno = e.deptno)

   DEPTNO DNAME          ENAME
   ------ -------------- ----------
       20 RESEARCH       SMITH
       30 SALES          ALLEN
       30 SALES          WARD
       20 RESEARCH       JONES
       30 SALES          MARTIN
       30 SALES          BLAKE
       10 ACCOUNTING     CLARK
       20 RESEARCH       SCOTT
       10 ACCOUNTING     KING
       30 SALES          TURNER
       20 RESEARCH       ADAMS
       30 SALES          JAMES
       20 RESEARCH       FORD
       10 ACCOUNTING     MILLER
       40 OPERATIONS

This next query returns rows from table EMP and any matching rows from table DEPT (if any):

 
 select d.deptno,d.dname,e.ename
   from dept d right outer join emp e
     on (d.deptno = e.deptno)

  DEPTNO DNAME          ENAME
  ------ -------------- ----------
      10 ACCOUNTING     MILLER
      10 ACCOUNTING     KING
      10 ACCOUNTING     CLARK
      20 RESEARCH       FORD
      20 RESEARCH       ADAMS
      20 RESEARCH       SCOTT
      20 RESEARCH       JONES
      20 RESEARCH       SMITH
      30 SALES          JAMES
      30 SALES          TURNER
      30 SALES          BLAKE
      30 SALES          MARTIN
      30 SALES          WARD
      30 SALES          ALLEN    
                        YODA

The results from these two queries are unioned to provide the final result set.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table

Performing Outer Joins when Using Aggregates - SQL Server

2008-06-04T03:15:00.000-07:00

Problem

Begin with the same problem as in 3.9, but modify table EMP_BONUS such that the difference in this case is not all employees in department 10 have been given bonuses. Consider the EMP_BONUS table and a query to (ostensibly) find both the sum of all salaries for department 10 and the sum of all bonuses for all employees in department 10:

 
 select * from emp_bonus

      EMPNO RECEIVED          TYPE
 ---------- ----------- ----------
       7934 17-MAR-2005          1
       7934 15-FEB-2005          2

 
 select deptno,
        sum(sal) as total_sal,
        sum(bonus) as total_bonus
   from (
 select e.empno,
        e.ename,
        e.sal,
        e.deptno,
        e.sal*case when eb.type = 1 then .1
                   when eb.type = 2 then .2
                   else .3 end as bonus
   from emp e, emp_bonus eb
  where e.empno  = eb.empno
    and e.deptno = 10
        )
  group by deptno

  DEPTNO  TOTAL_SAL TOTAL_BONUS
  ------ ---------- -----------
      10       2600         390

The result for TOTAL_BONUS is correct, but the value returned for TOTAL_SAL does not represent the sum of all salaries in department 10. The following query shows why the TOTAL_SAL is incorrect:

 
 select e.empno,
        e.ename,
        e.sal,
        e.deptno,
        e.sal*case when eb.type = 1 then .1
                   when eb.type = 2 then .2
              else .3 end as bonus
   from emp e, emp_bonus eb
  where e.empno = eb.empno
    and e.deptno = 10

     EMPNO ENAME          SAL     DEPTNO      BONUS
 --------- ---------  ------- ---------- ----------
      7934 MILLER        1300         10        130
      7934 MILLER        1300         10        260

Rather than sum all salaries in department 10, only the salary for "MILLER" is summed and it is erroneously summed twice. Ultimately, you would like to return the following result set:

 DEPTNO TOTAL_SAL TOTAL_BONUS
 ------ --------- -----------
     10      8750         390

Solution

The solution is similar to that of 3.9, but here you outer join to EMP_BONUS to ensure all employees from department 10 are included.

DB2, MySQL, PostgreSQL, SQL Server

Outer join to EMP_BONUS, then perform the sum on only distinct salaries from department 10:

  1 select deptno,
  2        sum(distinct sal) as total_sal,
  3        sum(bonus) as total_bonus
  4   from (
  5 select e.empno,
  6        e.ename,
  7        e.sal,
  8        e.deptno,
  9        e.sal*case when eb.type is null then 0
 10                   when eb.type = 1 then .1
 11                   when eb.type = 2 then .2
 12                   else .3 end as bonus
 13   from emp e left outer join emp_bonus eb
 14     on (e.empno = eb.empno)
 15  where e.deptno = 10
 16        )
 17  group by deptno

You can also use the window function SUM OVER:

  1 select distinct deptno,total_sal,total_bonus
  2   from (
  3 select e.empno,
  4        e.ename,
  5        sum(distinct e.sal) over
  6        (partition by e.deptno) as total_sal,
  7        e.deptno,
  8        sum(e.sal*case when eb.type is null then 0
  9                       when eb.type = 1 then .1
 10                       when eb.type = 2 then .2
 11                       else .3
 12                  end) over
 13        (partition by deptno) as total_bonus
 14   from emp e left outer join emp_bonus eb
 15     on (e.empno = eb.empno)
 16  where e.deptno = 10
 17        ) x

Oracle

If you are using Oracle9i Database or later you can use the preceding solution. Alternatively, you can use the proprietary Oracle outer-join syntax, which is mandatory for users on Oracle8i Database and earlier:

  1 select deptno,
  2        sum(distinct sal) as total_sal,
  3        sum(bonus) as total_bonus
  4   from (
  5 select e.empno,
  6        e.ename,
  7        e.sal,
  8        e.deptno,
  9        e.sal*case when eb.type is null then 0
 10                   when eb.type = 1 then .1
 11                   when eb.type = 2 then .2
 12                   else .3 end as bonus
 13   from emp e, emp_bonus eb
 14  where e.empno  = eb.empno (+)
 15    and e.deptno = 10
 16        )
 17  group by deptno

Oracle 8i Database users can also use the SUM OVER syntaxshown for DB2 and the other databases, but must modify it to use the proprietary Oracle outer-join syntax shown in the preceding query.

Discussion

The second query in the "Problem" section of this recipe joins table EMP and table EMP_BONUS and returns only rows for employee "MILLER", which is what causes the error on the sum of EMP.SAL (the other employees in DEPTNO 10 do not have bonuses and their salaries are not included in the sum). The solution is to outer join table EMP to table EMP_BONUS so even employees without a bonus will be included in the result. If an employee does not have a bonus, NULL will be returned for EMP_BONUS.TYPE. It is important to keep this in mind as the CASE statement has been modified and is slightly different from solution 3.9. If EMP_BONUS.TYPE is NULL, the CASE expression returns zero, which has no effect on the sum.

The following query is an alternative solution. The sum of all salaries in department 10 is computed first, then joined to table EMP, which is then joined to table EMP_BONUS (thus avoiding the outer join). The following query works for all DBMSs:

 
 select d.deptno,
        d.total_sal,
        sum(e.sal*case when eb.type = 1 then .1
                       when eb.type = 2 then .2
                       else .3 end) as total_bonus
   from emp e,
        emp_bonus eb,
       (
 select deptno, sum(sal) as total_sal
   from emp
  where deptno = 10
  group by deptno
        ) d
  where e.deptno = d.deptno
    and e.empno = eb.empno
  group by d.deptno,d.total_sal

    DEPTNO  TOTAL_SAL TOTAL_BONUS
 --------- ---------- -----------
        10       8750         390

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table