MySQL, SQL Server, PostgreSQL, SQLite support using the percent sign as the modulus:

    WHERE column % 2 = 1

For Oracle, you have to use the [MOD function][1]:

    WHERE MOD(column, 2) = 1


  [1]: http://techonthenet.com/oracle/functions/mod.php

At least some versions of SQL (Oracle, Informix, DB2, ISO Standard) support:

    WHERE MOD(value, 2) = 1

MySQL supports &#39;%&#39; as the modulus operator:

    WHERE value % 2 = 1

**Note:** *Disregard this answer, as I must have misunderstood the question.*

    select *
      from Table
      where len(ColName) mod 2 = 1

The exact syntax depends on what flavor of SQL you&#39;re using.

`select * from table where value % 2 = 1` works fine in mysql.

I&#39;m trying to get output value from DB via ADO.NET. There&#39;s a client code:  

        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(&quot;pDoSomethingParamsRes&quot;, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(&quot;@i&quot;, 1);
            var outParam = new SqlParameter(&quot;@out&quot;, SqlDbType.VarChar);
            outParam.Direction = ParameterDirection.Output;
            command.Parameters.Add(outParam);
            command.ExecuteNonQuery();
            Console.WriteLine(command.Parameters[&quot;@out&quot;].Value.ToString());
        }

When I run this I get the following exception:

    the Size property has an invalid size of 0
According to manual [SqlParameter.Size Property][1] I might omit size. Why do I get this exception?

How to make it work without passing size?  



  [1]: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.size.aspx

ADO.NET - The Size property has an invalid size of 0

What is the difference between using `andReturn(T value)` vs `andStubReturn(T value)` for EasyMock?

In what situation would you use `andStubReturn()` where `andReturn()` can&#39;t achieve the same result? 


EasyMock andReturn() vs andStubReturn()

There is a column in options that hold an integer. I want to select the row only if that value % 2 = 1.

I know this can be done in 2 queries but is it possible to do it in 1?

Selecting rows where remainder (modulo) is 1 after division by 2?

<p>There is a column in options that hold an integer. I want to select the row only if that value % 2 = 1.</p>
<p>I know this can be done in 2 queries but is it possible to do it in 1?</p>


I want to update the code on all my record to what they currently are plus _standard any ideas?

So for example if the codes are apple_1 and apple_2 I need them to be apple_1_standard and apple_2_standard  

Before:
-
    id   code
    ------------
    1    apple_1 
    1    apple_2

Psuedo Query:
-
    update categories set code = code + &quot;_standard&quot; where id = 1;


Expected result:
-
    id   code
    ----------------------
    1    apple_1_standard 
    1    apple_2_standard

How can I append a string to an existing field in MySQL?

I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ? 

So considering performance and other aspects when and why should I prefer one over the other ?

Stored Procedures Vs. Views

I tried to truncate a table with foreign keys and got the message:

&gt; &quot;**Cannot truncate table  because it is being referenced by a FOREIGN KEY constraint**&quot;. 

I read a lot of literature about the problem and thought that I found the solution by using delete 

    DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

But I still got an error message:

&gt; &quot;**The DELETE statement conflicted with the REFERENCE constraint**&quot;. 

When I try to delete with Microsoft Management Studio and execute the previous query 
    
    DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

it doesn&#39;t give an error and works properly. I want to delete all information from a table and add new into it, but I don&#39;t want to drop and create foreign keys.



I got error &quot;The DELETE statement conflicted with the REFERENCE constraint&quot;

 I am surprised to see that IS NULL and =NULL are yielding different results in a select query. What is difference between them? When to use what. I would be glad if you can explain me in detail. 

Is there any difference between IS NULL and =NULL

Background: I have a performance-critical query I&#39;d like to run and I don&#39;t care about dirty reads.

My question is; If I&#39;m using joins, do I have to specify the NOLOCK hint on those as well?

For instance; is:

    SELECT * FROM table1 a WITH (NOLOCK)
    INNER JOIN table2 b WITH (NOLOCK) ON a.ID = b.ID

Equivalent to:

    SELECT * FROM table1 a WITH (NOLOCK)
    INNER JOIN table2 b ON a.ID = b.ID

Or will I need to specify the `(NOLOCK)` hint on the join to ensure I&#39;m not locking the joined table?


SQL Server NOLOCK and joins

I need to load a table with a large amount of test data. This is to be used for testing performance and scaling.

How can I easily create 100,000 rows of random/junk data for my database table?

Fill database tables with a large amount of test data

I have heard that when developing application which uses a database you should do database unit testing.

What are the best practices in database unit testing? What are the primary concerns when doing DB unit testing and how to do it &quot;right&quot;?

How to do database unit testing?

I thought I was an experienced Maven user, but I am having a mental block on how to do this!

I&#39;ve been able to use the Maven sql plugin to drop, create, and install a schema in a database via plugin executions I&#39;ve defined and bound to the `pre-integration-test` phase.

However, now I&#39;d like to use that same sql plugin to insert some sample data whenever I want from the command line -- that is, not bound to any lifecycle goal.  There are a few different sets of sample data, so I&#39;d like to define a few different executions.

But is there a way to run one of these executions from the command line by using the execution ID perhaps?

Run a single Maven plugin execution?

Is email address a bad candidate for primary when compared to auto incrementing numbers?

Our web  application needs the email address to be unique in the system. So, I thought of using email address as primary key. However my colleague suggests that string comparison will be slower than integer comparison. 

Is it a valid reason to not use email as primary key?

We are using `PostgreSQL`.

Use email address as primary key?

I need a database of Countries and their Cities.

Any idea where I can get such a list?

Of Countries and their Cities

    public class ClassA
    {
         public string MyString {get; set;}
    }

    public class ClassB
    {
         public List&lt;ClassA&gt; MyObjects {get; set;}
    }

    List&lt;ClassB&gt; classBList = new List&lt;ClassB&gt;();
    var results = (from i in classBList select i.MyObjects).Distinct();

I want a distinct list of all the `ClassA` objects in the `classBList`.  How do I go about this using **LINQ**? I&#39;m thinking about a nested query, but couldn&#39;t quite figure it out.  Any help is very appreciated.

Using LINQ, select list of objects inside another list of objects

Can anyone tell me if a MySQL `SELECT` query is case sensitive or case insensitive by default? And if not, what query would I have to send so that I can do something like:

    SELECT * FROM `table` WHERE `Value` = &quot;iaresavage&quot;

Where in actuality, the real value of `Value` is `IAreSavage`.


MySQL: is a SELECT statement case sensitive?

In Oracle, it&#39;s possible to do a `SELECT` statement that returns the row number as a column in your result set.
 
For example,

    SELECT rownum, column1, column2 FROM table

returns:

&lt;pre&gt;
&lt;b&gt;rownum&lt;/b&gt;       &lt;b&gt;column1&lt;/b&gt;       &lt;b&gt;column2&lt;/b&gt;
1            Joe           Smith
2            Bob           Jones
&lt;/pre&gt;


But I don&#39;t want to specify each column by hand. I want to do something like:

    select rownum,* from table

&lt;pre&gt;
&lt;b&gt;rownum&lt;/b&gt;       &lt;b&gt;column1&lt;/b&gt;       &lt;b&gt;column2&lt;/b&gt;       &lt;b&gt;column3&lt;/b&gt;       &lt;b&gt;column4&lt;/b&gt;
1            Joe           Smith         1             2
2            Bob           Jones         3             4
&lt;/pre&gt;

Any ideas?

How do I select all the columns from a table, plus additional columns like ROWNUM?

So I&#39;ve been looking through the internet the last hour, reading and looking for the definitive answer to this simple question.

What is the default JOIN in MySQL?

    SELECT * FROM t1 JOIN t2

Is that the same as

    SELECT * FROM t1, t2
    
    OR

    SELECT * FROM t1 INNER JOIN t2


Also a related question, when you use &quot;WHERE&quot; clauses, is it the same as JOIN or INNER JOIN ?

Right now I&#39;m thinking a stand-alone JOIN is identical to using commas and WHERE clauses.

What is the default MySQL JOIN behaviour, INNER or OUTER?

I am using the following to allow my users to select their sex in their profile. 

    &lt;%= f.select (:sex, %w{ Male Female }) %&gt;

How would I create a blank value that the list would default to if nothing has been passed to the user.sex column? I am simply passing male or female as a string. 

The purpose is I want a blank value so a validation can make sure they are aware they have to select it. 

How do I set a blank value for an f.select form field

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column...

My table is a very simple linking setup for applying flags to a user ...

    ID   contactid  flag        flag_type 
    -----------------------------------
    118  99         Volunteer   1 
    119  99         Uploaded    2 
    120  100        Via Import  3 
    121  100        Volunteer   1  
    122  100        Uploaded    2

etc... in this case you&#39;ll see both contact 99 and 100 are flagged as both &quot;Volunteer&quot; and &quot;Uploaded&quot;...

What I need to be able to do is return those contactid&#39;s ONLY that match multiple criteria entered via a search form...the contactid&#39;s have to match ALL chosen flags... in my head the SQL should look something like:

    SELECT contactid 
     WHERE flag = &#39;Volunteer&#39; 
       AND flag = &#39;Uploaded&#39;...

but... that returns nothing... What am I doing wrong here?

SELECTING with multiple WHERE conditions on same column

What is the best way to include an input param in the `WHERE` clause but exclude it if it is null?

There are a number of ways I believe, but I can&#39;t seem to remember then.

Also could I use the `COALESCE()`? But I think this is only for SELECTing values?

Edit
---

To clarify, let&#39;s say a variable called `@code =&quot;1&quot;` then my where would be `Where type=&#39;B&#39; AND code = @code` but if `@code is null` then I only want `Where type=&#39;B&#39;` - notice the missing `code = @code`.


Checking an input param if not Null and using it in where in SQL Server

I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character. 

But this query is only retrieving those settings where character is = 1, not the default settings if the user havent setted anyone.

    SELECT `settings`.*, `character_settings`.`value`
    FROM (`settings`)
    LEFT JOIN `character_settings` 
    ON `character_settings`.`setting_id` = `settings`.`id`
    WHERE `character_settings`.`character_id` = &#39;1&#39;  

So i should need something like this:

    array(
        &#39;0&#39; =&gt; array(&#39;somekey&#39; =&gt; &#39;keyname&#39;, &#39;value&#39; =&gt; &#39;thevalue&#39;),
        &#39;1&#39; =&gt; array(&#39;somekey2&#39; =&gt; &#39;keyname2&#39;),
        &#39;2&#39; =&gt; array(&#39;somekey3&#39; =&gt; &#39;keyname3&#39;)
    )
Where key 1 and 2 are the default values when key 0 contains the default value with the character value.

Left Join With Where Clause

Is there a way to detect if a value is a number in a MySQL query? Such as

    SELECT * 
    FROM myTable 
    WHERE isANumber(col1) = true

Detect if value is number in MySQL

Is it possible to negate a where clause?

e.g.

    DELETE * FROM table WHERE id != 2;

SQL WHERE condition is not equal to?

I understand the Modulus operator in terms of the following expression:

    7 % 5

This would return 2 due to the fact that 5 goes into 7 once and then gives the 2 that is left over, however my confusion comes when you reverse this statement to read:

    5 % 7

This gives me the value of 5 which confuses me slightly. Although the whole of 7 doesn&#39;t go into 5, part of it does so why is there either no remainder or a remainder of positive or negative 2? 

If it is calculating the value of 5 based on the fact that 7 doesn&#39;t go into 5 at all why is the remainder then not 7 instead of 5?

I feel like there is something I&#39;m missing here in my understanding of the modulus operator.

Understanding The Modulus Operator %

I get the following error when using code for an extension, I&#39;m not sure if they&#39;re asking to just use a different operator or modify the values in the expression based on an internet search.

Error: % is unavailable: Use truncatingRemainder instead

Extension code:

    extension CMTime {
        var durationText:String {
            let totalSeconds = CMTimeGetSeconds(self)
            let hours:Int = Int(totalSeconds / 3600)
            let minutes:Int = Int(totalSeconds % 3600 / 60)
            let seconds:Int = Int(totalSeconds % 60)
            
            if hours &gt; 0 {
                return String(format: &quot;%i:%02i:%02i&quot;, hours, minutes, seconds)
            } else {
                return String(format: &quot;%02i:%02i&quot;, minutes, seconds)
            }
        }
    }

The error(s) occur when setting the minutes and seconds variables.

Content Type	Original Author	Original Content on Stackoverflow
Question	Carlos	View Question on Stackoverflow
Solution 1 - Sql	OMG Ponies	View Answer on Stackoverflow
Solution 2 - Sql	Jonathan Leffler	View Answer on Stackoverflow
Solution 3 - Sql	David R Tribble	View Answer on Stackoverflow
Solution 4 - Sql	einarmagnus	View Answer on Stackoverflow

Selecting rows where remainder (modulo) is 1 after division by 2?

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

EasyMock andReturn() vs andStubReturn()

ADO.NET - The Size property has an invalid size of 0

Attributions