Rectangle 27 185

What are the different SQL JOINs ?

SQL JOIN is a method to retrieve data from two or more database tables.

There are a total of five JOINs. They are :

1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported.

In other words, INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.

INNER
OUTER
LEFT
JOIN
INNER JOIN
OUTER JOIN

Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).

There are three kinds of Outer Join :

2.1 LEFT OUTER JOIN or LEFT JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

2.2 RIGHT OUTER JOIN or RIGHT JOIN

This JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

2.3 FULL OUTER JOIN or FULL JOIN

This JOIN combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns row from either table when the conditions are met and returns NULL value when there is no match.

In other words, OUTER JOIN is based on the fact that : ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

It is based on the two conditions :

  • the JOIN is made on all the columns with the same name for equality.
  • Removes duplicate columns from the result.

This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).

It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

Depending on the operator used for a JOIN clause, there can be two types of JOINs. They are

For whatever JOIN type (INNER, OUTER, etc), if we use ONLY the equality operator (=), then we say that the JOIN is an EQUI JOIN.

This is same as EQUI JOIN but it allows all other operators like >, <, >= etc.

Many consider both EQUI JOIN and Theta JOIN similar to INNER, OUTER etc JOINs. But I strongly believe that its a mistake and makes the ideas vague. Because INNER JOIN, OUTER JOIN etc are all connected with the tables and their data where as EQUI JOIN and THETA JOIN are only connected with the operators we use in the former.

Again, there are many who consider NATURAL JOIN as some sort of "peculiar" EQUI JOIN. In fact, it is true, because of the first condition I mentioned for NATURAL JOIN. However, we dont have to restrict that simply to NATURAL JOINs alone. INNER JOINs, OUTER JOINs etc could be an EQUI JOIN too.

While this seems reasonable, I don't think answers "what is an SQL join" in any way that conveys useful information. The answer as a whole is a reference written for people who already understand joins, not for the sorts of people who are asking those questions. It also omits references, both to support its claims (as is appropriate if making an authorative answer) and to provide additional explanation via external resources. If you're trying to write an authoritative answer to link new SQL users to, it might be worth filling in the blanks a bit, especially the "what is a join" part.

database - SQL JOIN and different types of JOINs - Stack Overflow

sql database oracle postgresql rdbms
Rectangle 27 134

To Accomplish multiple LIKE filters with Wildcards:

SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |1940 ';

Values within REGEXP quotes and between the | (OR) operator are treated as wildcards. Typically, REGEXP will require wildcard expressions such as (.*)1740 (.*) to work as %1740 %.

If you need more control over placement of the wildcard, use some of these variants:

To Accomplish LIKE with Controlled Wildcard Placement:

SELECT * FROM fiberbox WHERE field REGEXP '^1740 |1938 $|1940 (.*) test';

There are more efficient ways to narrow down specific matches, but that requires more review of Regular Expressions. NOTE: Not all regex patterns appear to work in MySQL statements. You'll need to test your patterns and see what works.

Finally, To Accomplish Multiple LIKE and NOT LIKE filters:

SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 |^9999$'
                          OR field NOT REGEXP '1940 |^test ';
SELECT * FROM fiberbox WHERE field REGEXP '1740 |1938 '
                          OR field NOT REGEXP '1940 |^test '
                          OR field NOT LIKE 'test %'
                          OR field = '9999';

Notice I separated the NOT set in a separate WHERE filter. I experimented with using negating patterns, forward looking patterns, and so on. However, these expressions did not appear to yield the desired results. In the first example above, I use ^9999$ to indicate exact match. This allows you to add specific matches with wildcard matches in the same expression. However, you can also mix these types of statements as you can see in the second example listed.

Regarding performance, I ran some minor tests against an existing table and found no differences between my variations. However, I imagine performance could be an issue with bigger databases, larger fields, greater record counts, and more complex filters.

As always, use logic above as it makes sense.

If you want to learn more about regular expressions, I recommend www.regular-expressions.info as a good reference site.

Keep in mind that a field with the value NULL won't match REGEXP. You can use IFNULL to solve this problem. WHERE IFNULL(field, '') NOT REGEXP '1740 | 1938'

sql - MySQL LIKE IN()? - Stack Overflow

sql mysql
Rectangle 27 184

What are the different SQL JOINs ?

SQL JOIN is a method to retrieve data from two or more database tables.

There are a total of five JOINs. They are :

1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported.

In other words, INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.

INNER
OUTER
LEFT
JOIN
INNER JOIN
OUTER JOIN

Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).

There are three kinds of Outer Join :

2.1 LEFT OUTER JOIN or LEFT JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

2.2 RIGHT OUTER JOIN or RIGHT JOIN

This JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

2.3 FULL OUTER JOIN or FULL JOIN

This JOIN combines LEFT OUTER JOIN and RIGHT OUTER JOIN. It returns row from either table when the conditions are met and returns NULL value when there is no match.

In other words, OUTER JOIN is based on the fact that : ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

It is based on the two conditions :

  • the JOIN is made on all the columns with the same name for equality.
  • Removes duplicate columns from the result.

This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).

It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

Depending on the operator used for a JOIN clause, there can be two types of JOINs. They are

For whatever JOIN type (INNER, OUTER, etc), if we use ONLY the equality operator (=), then we say that the JOIN is an EQUI JOIN.

This is same as EQUI JOIN but it allows all other operators like >, <, >= etc.

Many consider both EQUI JOIN and Theta JOIN similar to INNER, OUTER etc JOINs. But I strongly believe that its a mistake and makes the ideas vague. Because INNER JOIN, OUTER JOIN etc are all connected with the tables and their data where as EQUI JOIN and THETA JOIN are only connected with the operators we use in the former.

Again, there are many who consider NATURAL JOIN as some sort of "peculiar" EQUI JOIN. In fact, it is true, because of the first condition I mentioned for NATURAL JOIN. However, we dont have to restrict that simply to NATURAL JOINs alone. INNER JOINs, OUTER JOINs etc could be an EQUI JOIN too.

While this seems reasonable, I don't think answers "what is an SQL join" in any way that conveys useful information. The answer as a whole is a reference written for people who already understand joins, not for the sorts of people who are asking those questions. It also omits references, both to support its claims (as is appropriate if making an authorative answer) and to provide additional explanation via external resources. If you're trying to write an authoritative answer to link new SQL users to, it might be worth filling in the blanks a bit, especially the "what is a join" part.

database - SQL JOIN and different types of JOINs - Stack Overflow

sql database oracle postgresql rdbms
Rectangle 27 755

SQL Server Data Types and Their .NET Framework Equivalents

The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

SQL Server data type          CLR data type (SQL Server)    CLR data type (.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None
timestamp                     None                          None 
xml                           SqlXml                        None

int in .NET is the same as Int32 in this table, so it would be a int in SQL Server as well.

.net - C# Equivalent of SQL Server DataTypes - Stack Overflow

c# .net sql-server
Rectangle 27 733

SQL Server Data Types and Their .NET Framework Equivalents

The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

SQL Server data type          CLR data type (SQL Server)    CLR data type (.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None
timestamp                     None                          None 
xml                           SqlXml                        None

If I have int in c# what it will be in SQL server 2012? smallint or int?

int in .NET is the same as Int32 in this table, so it would be a int in SQL Server as well.

.net - C# Equivalent of SQL Server DataTypes - Stack Overflow

c# .net sql-server
Rectangle 27 389

Acrylic DNS Proxy (free, open source) does the job. It creates a proxy DNS server (on your own computer) with its own hosts file. The hosts file accepts wildcards.

Download from the offical website

To configure Acrylic DNS Proxy, install it from the above link then go to:

Add the folowing lines on the end of the file:

127.0.0.1   *.localhost
127.0.0.1   *.local
127.0.0.1   *.lc

Restart the Acrylic DNS Proxy service:

You will also need to adjust your DNS setting in you network interface settings:

  • Network and Internet

Set "Use the following DNS server address":

Preferred DNS Server: 127.0.0.1
VirtualDocumentRoot

Please upvote this answer. This is by far best answer for the problem posed by that question. Combine it with VirtualDocumentRoot and you have comfy work environment.

Start -> Control Panel -> Network and Internet -> Network Connections -> Local Area Connection -> Properties -> Internet Protocol Version 4 -> Properties -> Use the following DNS server addresses: -> Preferred DNS Server -> 127.0.0.1

Just to be clear, the "custom hosts file" is AcrylicHosts.txt, not AcrylicConfiguration.ini. Maybe that should be obvious, but it confused me for a while.

@SB2055 check your DNS settings in your network properties is still correct.

apache - Wildcards in a Windows hosts file - Stack Overflow

windows apache dns hosts virtual-hosts
Rectangle 27 385

Acrylic DNS Proxy (free, open source) does the job. It creates a proxy DNS server (on your own computer) with its own hosts file. The hosts file accepts wildcards.

Download from the offical website

To configure Acrylic DNS Proxy, install it from the above link then go to:

Add the folowing lines on the end of the file:

127.0.0.1   *.localhost
127.0.0.1   *.local
127.0.0.1   *.lc

Restart the Acrylic DNS Proxy service:

You will also need to adjust your DNS setting in you network interface settings:

  • Network and Internet

Set "Use the following DNS server address":

Preferred DNS Server: 127.0.0.1
VirtualDocumentRoot

Please upvote this answer. This is by far best answer for the problem posed by that question. Combine it with VirtualDocumentRoot and you have comfy work environment.

Start -> Control Panel -> Network and Internet -> Network Connections -> Local Area Connection -> Properties -> Internet Protocol Version 4 -> Properties -> Use the following DNS server addresses: -> Preferred DNS Server -> 127.0.0.1

Just to be clear, the "custom hosts file" is AcrylicHosts.txt, not AcrylicConfiguration.ini. Maybe that should be obvious, but it confused me for a while.

@SB2055 check your DNS settings in your network properties is still correct.

apache - Wildcards in a Windows hosts file - Stack Overflow

windows apache dns hosts virtual-hosts
Rectangle 27 718

SQL Server Data Types and Their .NET Framework Equivalents

The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

SQL Server data type          CLR data type (SQL Server)    CLR data type (.NET Framework)  
varbinary                     SqlBytes, SqlBinary           Byte[]  
binary                        SqlBytes, SqlBinary           Byte[]  
varbinary(1), binary(1)       SqlBytes, SqlBinary           byte, Byte[] 
image                         None                          None

varchar                       None                          None
char                          None                          None
nvarchar(1), nchar(1)         SqlChars, SqlString           Char, String, Char[]     
nvarchar                      SqlChars, SqlString           String, Char[] 
nchar                         SqlChars, SqlString           String, Char[] 
text                          None                          None
ntext                         None                          None

uniqueidentifier              SqlGuid                       Guid 
rowversion                    None                          Byte[]  
bit                           SqlBoolean                    Boolean 
tinyint                       SqlByte                       Byte 
smallint                      SqlInt16                      Int16  
int                           SqlInt32                      Int32  
bigint                        SqlInt64                      Int64 

smallmoney                    SqlMoney                      Decimal  
money                         SqlMoney                      Decimal  
numeric                       SqlDecimal                    Decimal  
decimal                       SqlDecimal                    Decimal  
real                          SqlSingle                     Single  
float                         SqlDouble                     Double  

smalldatetime                 SqlDateTime                   DateTime  
datetime                      SqlDateTime                   DateTime 

sql_variant                   None                          Object  
User-defined type(UDT)        None                          user-defined type     
table                         None                          None 
cursor                        None                          None
timestamp                     None                          None 
xml                           SqlXml                        None

If I have int in c# what it will be in SQL server 2012? smallint or int?

int in .NET is the same as Int32 in this table, so it would be a int in SQL Server as well.

.net - C# Equivalent of SQL Server DataTypes - Stack Overflow

c# .net sql-server
Rectangle 27 272

SQL

It is more or less standardized, and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.

PL/SQL is a proprietary procedural language used by Oracle

PL/pgSQL

TSQL is a proprietary procedural language used by Microsoft in SQL Server.

Procedural languages are designed to extend SQL's abilities while being able to integrate well with SQL. Several features such as local variables and string/data processing are added. These features make the language Turing-complete.

They are also used to write stored procedures: pieces of code residing on the server to manage complex business rules that are hard or impossible to manage with pure set-based operations.

PL/SQL doesn't solely exist in the database, e.g. Oracle Forms contains a client-side implementation of PL/SQL.

Note each database has it's own implementation of SQL (T-SQL and PL/SQL being two of the more common), ANSII standard SQL is often used when multiple backends are used, but not all databases use all ANSII SQL features either. And the database specific implementation of SQl tends to be the one that performs best for that database - after all they have to give you a reason to use their database, right?

Correction: SQL is a language to operate on tables for which SQL itself provides the definition. The SQL Standard avoids the words 'set' and 'relation' and their derivatives. SQL tables are not sets.

It sure would be nice to have a sql procedural language that would work across oracle and sql server.

tsql - What is the difference between SQL, PL-SQL and T-SQL? - Stack O...

sql tsql plsql
Rectangle 27 1030

To get all the elements starting with "jander" you should use:

$("[id^=jander]")

To get those that end with "jander"

$("[id$=jander]")
$('input[name^="news"]').val('news here!')

The code works as intended. There is no need to double quote, it just increases the chances of missing a closing quote and makes it less readable.

@nico Interestingly enough, docs say it works with attributes and id is a technically a property, but I guess with the the more recent releases of jquery (i.e. 1.9) and how the latest changes to attributes and properties are hanlded, the line is slightly blurred with respect to the two and so your able to use the attribute selectors for (at least some) properties.

@johntrepreneur: good point, I did not notice that!

What if, I want to select the Even of those selected. for instance: I currently have .col-lg-4:even div:nth-child(1) if I wanted to do the same.... what would I write? "[class^=.col-lg-]:even"? (I don't seem to make it work)

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

Wildcards in jQuery selectors - Stack Overflow

jquery jquery-selectors sizzle
Rectangle 27 1016

To get all the elements starting with "jander" you should use:

$("[id^=jander]")

To get those that end with "jander"

$("[id$=jander]")
$('input[name^="news"]').val('news here!')

The code works as intended. There is no need to double quote, it just increases the chances of missing a closing quote and makes it less readable.

@nico Interestingly enough, docs say it works with attributes and id is a technically a property, but I guess with the the more recent releases of jquery (i.e. 1.9) and how the latest changes to attributes and properties are hanlded, the line is slightly blurred with respect to the two and so your able to use the attribute selectors for (at least some) properties.

@johntrepreneur: good point, I did not notice that!

What if, I want to select the Even of those selected. for instance: I currently have .col-lg-4:even div:nth-child(1) if I wanted to do the same.... what would I write? "[class^=.col-lg-]:even"? (I don't seem to make it work)

Wildcards in jQuery selectors - Stack Overflow

jquery jquery-selectors sizzle
Rectangle 27 63

The patterns for http.Handler and http.HandleFunc aren't regular expressions or globs. There isn't a way to specify wildcards. They're documented here.

That said, it's not too hard to create your own handler that can use regular expressions or any other kind of pattern you want. Here's one that uses regular expressions (compiled, but not tested):

type route struct {
    pattern *regexp.Regexp
    handler http.Handler
}

type RegexpHandler struct {
    routes []*route
}

func (h *RegexpHandler) Handler(pattern *regexp.Regexp, handler http.Handler) {
    h.routes = append(h.routes, &route{pattern, handler})
}

func (h *RegexpHandler) HandleFunc(pattern *regexp.Regexp, handler func(http.ResponseWriter, *http.Request)) {
    h.routes = append(h.routes, &route{pattern, http.HandlerFunc(handler)})
}

func (h *RegexpHandler) ServeHTTP(w http.ResponseWriter, r *http.Request) {
    for _, route := range h.routes {
        if route.pattern.MatchString(r.URL.Path) {
            route.handler.ServeHTTP(w, r)
            return
        }
    }
    // no pattern matched; send 404 response
    http.NotFound(w, r)
}

As I wanted ruby on rails style routes in Go, I have started the goweb project (see goweb.googlecode.com) that allows this style of route mapping: goweb.MapFunc("/people/{person_id}", handler)

Gorillatoolkit does a great job of implementing PAT and MUX for processing routes. The only problem is that it is slow and I have yet to review the code. At least in their API it's possible to name the params... which is the whole point of that sort of functionality. The code above does not provide anything that sophisticated and is likely not useful without named elements.

the pattern "/" matches all paths not matched by other registered patterns
(h *RegexpHandler) Handle
(h *RegexpHandler) Handler
ServeHTTP
RegexpHandler

regex - Wildcards in the pattern for http.HandleFunc - Stack Overflow

regex go
Rectangle 27 27

This is my independent take on this question that I use for my own work. It works in SQL2000 and greater, allows wildcards, column filtering, and will search most of the normal data types.

select * from * where any like 'foo'
--------------------------------------------------------------------------------
-- Search all columns in all tables in a database for a string.
-- Does not search: image, sql_variant or user-defined types.
-- Exact search always for money and smallmoney; no wildcards for matching these.
--------------------------------------------------------------------------------
declare @SearchTerm nvarchar(4000) -- Can be max for SQL2005+
declare @ColumnName sysname

--------------------------------------------------------------------------------
-- SET THESE!
--------------------------------------------------------------------------------
set @SearchTerm = N'foo' -- Term to be searched for, wildcards okay
set @ColumnName = N'' -- Use to restrict the search to certain columns, wildcards okay, null or empty string for all cols
--------------------------------------------------------------------------------
-- END SET
--------------------------------------------------------------------------------

set nocount on

declare @TabCols table (
      id int not null primary key identity
    , table_schema sysname not null
    , table_name sysname not null
    , column_name sysname not null
    , data_type sysname not null
)
insert into @TabCols (table_schema, table_name, column_name, data_type)
    select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
    from INFORMATION_SCHEMA.TABLES t
        join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA
            and t.TABLE_NAME = c.TABLE_NAME
    where 1 = 1
        and t.TABLE_TYPE = 'base table'
        and c.DATA_TYPE not in ('image', 'sql_variant')
        and c.COLUMN_NAME like case when len(@ColumnName) > 0 then @ColumnName else '%' end
    order by c.TABLE_NAME, c.ORDINAL_POSITION

declare
      @table_schema sysname
    , @table_name sysname
    , @column_name sysname
    , @data_type sysname
    , @exists nvarchar(4000) -- Can be max for SQL2005+
    , @sql nvarchar(4000) -- Can be max for SQL2005+
    , @where nvarchar(4000) -- Can be max for SQL2005+
    , @run nvarchar(4000) -- Can be max for SQL2005+

while exists (select null from @TabCols) begin

    select top 1
          @table_schema = table_schema
        , @table_name = table_name
        , @exists = 'select null from [' + table_schema + '].[' + table_name + '] where 1 = 0'
        , @sql = 'select ''' + '[' + table_schema + '].[' + table_name + ']' + ''' as TABLE_NAME, * from [' + table_schema + '].[' + table_name + '] where 1 = 0'
        , @where = ''
    from @TabCols
    order by id

    while exists (select null from @TabCols where table_schema = @table_schema and table_name = @table_name) begin

        select top 1
              @column_name = column_name
            , @data_type = data_type
        from @TabCols
        where table_schema = @table_schema
            and table_name = @table_name
        order by id

        -- Special case for money
        if @data_type in ('money', 'smallmoney') begin
            if isnumeric(@SearchTerm) = 1 begin
                set @where = @where + ' or [' + @column_name + '] = cast(''' + @SearchTerm + ''' as ' + @data_type + ')' -- could also cast the column as varchar for wildcards
            end
        end
        -- Special case for xml
        else if @data_type = 'xml' begin
            set @where = @where + ' or cast([' + @column_name + '] as nvarchar(max)) like ''' + @SearchTerm + ''''
        end
        -- Special case for date
        else if @data_type in ('date', 'datetime', 'datetime2', 'datetimeoffset', 'smalldatetime', 'time') begin
            set @where = @where + ' or convert(nvarchar(50), [' + @column_name + '], 121) like ''' + @SearchTerm + ''''
        end
        -- Search all other types
        else begin
            set @where = @where + ' or [' + @column_name + '] like ''' + @SearchTerm + ''''
        end

        delete from @TabCols where table_schema = @table_schema and table_name = @table_name and column_name = @column_name

    end

    set @run = 'if exists(' + @exists + @where + ') begin ' + @sql + @where + ' print ''' + @table_name + ''' end'
    print @run
    exec sp_executesql @run

end

set nocount off

I don't put it in proc form since I don't want to maintain it across hundreds of DBs and it's really for ad-hoc work anyway. Please feel free to comment on bug-fixes.

Thanks, but I'm getting nothing but syntax errors on this in phpMyAdmin. Has something changed with SQL since this was written?

@NoBugs This is written in T-SQL for SQL Server.

@NoBugs: You need to encapsulate the code inside your own stored procedure or some other function.

Just to clarify the output, if running this from Microsoft SQL Server Management Studio, the Results tab will only pop open if the search term is found. If the search term is not found, only the Messages tab will open with the executed search statements. The Message tab does not include any results but will also open along with the Results tab when the search term is found.

This is great! Working with a purchased app where I don't want to be saving custom database objects into their schema. Thanks!

How do I find a value anywhere in a SQL Server Database? - Stack Overf...

sql-server
Rectangle 27 1013

To get all the elements starting with "jander" you should use:

$("[id^=jander]")

To get those that end with "jander"

$("[id$=jander]")
$('input[name^="news"]').val('news here!')

The code works as intended. There is no need to double quote, it just increases the chances of missing a closing quote and makes it less readable.

@nico Interestingly enough, docs say it works with attributes and id is a technically a property, but I guess with the the more recent releases of jquery (i.e. 1.9) and how the latest changes to attributes and properties are hanlded, the line is slightly blurred with respect to the two and so your able to use the attribute selectors for (at least some) properties.

@johntrepreneur: good point, I did not notice that!

What if, I want to select the Even of those selected. for instance: I currently have .col-lg-4:even div:nth-child(1) if I wanted to do the same.... what would I write? "[class^=.col-lg-]:even"? (I don't seem to make it work)

Wildcards in jQuery selectors - Stack Overflow

jquery jquery-selectors sizzle
Rectangle 27 1011

To get all the elements starting with "jander" you should use:

$("[id^=jander]")

To get those that end with "jander"

$("[id$=jander]")
$('input[name^="news"]').val('news here!')

The code works as intended. There is no need to double quote, it just increases the chances of missing a closing quote and makes it less readable.

@nico Interestingly enough, docs say it works with attributes and id is a technically a property, but I guess with the the more recent releases of jquery (i.e. 1.9) and how the latest changes to attributes and properties are hanlded, the line is slightly blurred with respect to the two and so your able to use the attribute selectors for (at least some) properties.

@johntrepreneur: good point, I did not notice that!

What if, I want to select the Even of those selected. for instance: I currently have .col-lg-4:even div:nth-child(1) if I wanted to do the same.... what would I write? "[class^=.col-lg-]:even"? (I don't seem to make it work)

Wildcards in jQuery selectors - Stack Overflow

jquery jquery-selectors sizzle
Rectangle 27 380

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123\', '')
WHERE ID <=4

(I also added the \ in the replace as I assume you don't need that either)

It work grate, but not with ntext type :( ...>> Msg 8116, Level 16, State 1, Line 21 - Argument data type ntext is invalid for argument 1 of replace function.

Wait, what is with the `? isn't that escaping the '` character and making this incorrect?

sql - UPDATE and REPLACE part of a string - Stack Overflow

sql sql-server string sql-server-2008 replace
Rectangle 27 84

The following query will produce different results (or errors) dependi...

There are two types of quotes in MySQL:

  • ` for enclosing identifiers such as table and column names

And then there is " which is a special case. It could be used for one of above-mentioned purposes at a time depending on MySQL server's sql_mode:

  • By default the " character can be used to enclose string literals just like '
  • In ANSI_QUOTES mode the " character can be used to enclose identifiers just like `
SELECT "column" FROM table WHERE foo = "bar"

The query will select the string literal "column" where column foo is equal to string "bar"

The query will select the column column where column foo is equal to column bar

  • I suggest that you avoid using " so that your code becomes independent of SQL modes
  • Always quote identifiers since it is a good practice (quite a few questions on SO discuss this)

sql - When to use single quotes, double quotes, and backticks in MySQL...

mysql sql quotes
Rectangle 27 90

You can specify table names on the command line one after the other, but without wildcards. mysqldump databasename table1 table2 table3

You can also use --ignore-table if that would be shorter.

Another idea is to get the tables into a file with something like

mysql -N information_schema -e "select table_name from tables where table_schema = 'databasename' and table_name like 'bak_%'" > tables.txt

Edit the file and get all the databases onto one line. Then do

mysqldump dbname `cat tables.txt` > dump_file.sql

To drop tables in one line (not recommended) you can do the following

But either way, I will have to list out 70 tables to ignore, or 430 tables to include. This is what I am trying to avoid doing. I should have been more clear in my question, but thanks for the answer. This WOULD work, yes ;)

mysql -N information_schema -e "select table_name from  tables where table_name like 'bak_%'"" > tables.txt
mysqldump dbname `cat tables.txt`

I ended up just dropping the bak_ tables, but I still used this to get the list of table. Then I gawked them together with commas to create a big DROP TABLE statement gawk '{print $1"," }' tables.txt > baktables.sql. Thanks!

Any way to do this in a one-liner? ie: without using a temp file as an intermediary?

mysql -NB  information_schema -e "select table_name from tables where table_name like 'bak_%'" | xargs -I"{}" mysql test -e "DROP TABLE {}"

mysql - Mysqldump only tables with certain prefix / Mysqldump wildcard...

mysql mysqldump
Rectangle 27 197

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to ...

You can simply add the two.

  • if the Time part of your Date column is always zero

and

Date part

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable
It works like this due to the way the date is stored as two 4-byte `Integers` with the
left 4-bytes being the `date` and the right 4-bytes being the `time`.  

Its like doing $0001 0000 + $0000 0001 = $0001 0001
Date
Time
SQL Server 2008
Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

@Jon, It's true so long as the time element of the date column and the date element of the time column are both zero.

You are most likely experiencing what is desribed here groups.google.be/group/borland*+author%3Ateamb*#1ab62659d8be3135

The "zero" date in SQL Server is 1900-01-01, no?

When i tried this I did not need to cast the 'time' value to datetime. In other words you can do: datetime + time

How to combine date from one field with time from another field - MS S...

sql sql-server datetime
Rectangle 27 251

Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS

Yes, the Standard approach is to use a case-insensitive collation, though the collations themselves are vendor-specific. Is yours SQL Server syntax?

In my case, I have 1 column in my db that is case-sensitive. I needed to compare it to a standard (CI) column. I used a variation of this WHERE Foo.Bar = (Baz.Bar COLLATE Latin1_General_CS_AS)

Thanks but what is Latin1_General_CS_AS ?? Is it special keyword?

@VijaySinghRana Latin1_General_CS_AS is a specification of the collation. Collation refers to a set of rules that determine how data is sorted and compared. See this page for more information.

SQL Case Sensitive String Compare - Stack Overflow

sql