[`CHARINDEX()`][1] searches for a substring within a larger string, and returns the position of the match, or 0 if no match is found

    if CHARINDEX(&#39;ME&#39;,@mainString) &gt; 0
    begin
        --do something
    end

*Edit* or from daniels answer, if you&#39;re wanting to find a word (and not subcomponents of words), your `CHARINDEX` call would look like:

    CHARINDEX(&#39; ME &#39;,&#39; &#39; + REPLACE(REPLACE(@mainString,&#39;,&#39;,&#39; &#39;),&#39;.&#39;,&#39; &#39;) + &#39; &#39;)

(Add more recursive REPLACE() calls for any other punctuation that may occur)

  [1]: http://msdn.microsoft.com/en-us/library/ms186323.aspx

You can just use wildcards in the predicate (after IF, WHERE or ON):

    @mainstring LIKE &#39;%&#39; + @substring + &#39;%&#39;

or in this specific case

    &#39; &#39; + @mainstring + &#39; &#39; LIKE &#39;% ME[., ]%&#39;

(Put the spaces in the quoted string if you&#39;re looking for the whole word, or leave them out if ME can be part of a bigger word).

How can I check if I have any uncommitted changes in my git repository:

1. Changes added to the index but not committed
2. Untracked files

from a script?

`git-status` seems to always return zero with git version 1.6.4.2.


Checking for a dirty index or untracked files with Git

I want to create an instance of a type that I specify in a generic method that I have. This type has a number of overloaded constructors. I&#39;d like to be able to pass arguments to the constructors, but 

    Activator.CreateInstance&lt;T&gt;()

doesn&#39;t see to have this as an option.

Is there another way to do it?


How to Pass Parameters to Activator.CreateInstance&lt;T&gt;()

I&#39;ve a string, `@mainString = &#39;CATCH ME IF YOU CAN&#39;`. I want to check whether the word `ME` is inside `@mainString`.

How do I check if a string has a specific substring in SQL?


Check if a string contains a substring in SQL Server 2005, using a stored procedure

<p>I've a string, <code>@mainString = 'CATCH ME IF YOU CAN'</code>. I want to check whether the word <code>ME</code> is inside <code>@mainString</code>.</p>
<p>How do I check if a string has a specific substring in SQL?</p>


I&#39;m building a report in Visual Studio 2008 with a lot of [multivalue parameters][1] and it&#39;s working great, but I would like to have have the &quot;(Select all)&quot; option as the default value when the report is opened. 

Is there some kind of expression or SQL code I can use to make this happen? Or do I need to choose &quot;(Select all)&quot; every time, in every parameter, each time I want to run the report?

  [1]: http://msdn.microsoft.com/en-us/library/aa337292%28v=sql.100%29.aspx

Select All as default value for Multivalue parameter

Compare these 2 queries. Is it faster to put the filter on the join criteria or in the `WHERE` clause. I have always felt that it is faster on the join criteria because it reduces the result set at the soonest possible moment, but I don&#39;t know for sure.

I&#39;m going to build some tests to see, but I also wanted to get opinions on which would is clearer to read as well.

**Query 1**

    SELECT      *
    FROM        TableA a
    INNER JOIN  TableXRef x
            ON  a.ID = x.TableAID
    INNER JOIN  TableB b
            ON  x.TableBID = b.ID
    WHERE       a.ID = 1            /* &lt;-- Filter here? */

**Query 2**

    SELECT      *
    FROM        TableA a
    INNER JOIN  TableXRef x
            ON  a.ID = x.TableAID
            AND a.ID = 1            /* &lt;-- Or filter here? */
    INNER JOIN  TableB b
            ON  x.TableBID = b.ID


**EDIT**

I ran some tests and the results show that it is actually very close, but the `WHERE` clause is actually slightly faster! =)

I absolutely agree that it makes more sense to apply the filter on the `WHERE` clause, I was just curious as to the performance implications.

**ELAPSED TIME WHERE CRITERIA:** 143016 ms  
**ELAPSED TIME JOIN CRITERIA:** 143256 ms

**TEST**

    SET NOCOUNT ON;

    DECLARE @num    INT,
            @iter   INT

    SELECT  @num    = 1000, -- Number of records in TableA and TableB, the cross table is populated with a CROSS JOIN from A to B
            @iter   = 1000  -- Number of select iterations to perform

    DECLARE @a TABLE (
            id INT
    )

    DECLARE @b TABLE (
            id INT
    )

    DECLARE @x TABLE (
            aid INT,
            bid INT
    )

    DECLARE @num_curr INT
    SELECT  @num_curr = 1
            
    WHILE (@num_curr &lt;= @num)
    BEGIN
        INSERT @a (id) SELECT @num_curr
        INSERT @b (id) SELECT @num_curr
        
        SELECT @num_curr = @num_curr + 1
    END

    INSERT      @x (aid, bid)
    SELECT      a.id,
                b.id
    FROM        @a a
    CROSS JOIN  @b b

    /*
        TEST
    */
    DECLARE @begin_where    DATETIME,
            @end_where      DATETIME,
            @count_where    INT,
            @begin_join     DATETIME,
            @end_join       DATETIME,
            @count_join     INT,
            @curr           INT,
            @aid            INT

    DECLARE @temp TABLE (
            curr    INT,
            aid     INT,
            bid     INT
    )

    DELETE FROM @temp

    SELECT  @curr   = 0,
            @aid    = 50

    SELECT  @begin_where = CURRENT_TIMESTAMP
    WHILE (@curr &lt; @iter)
    BEGIN
        INSERT      @temp (curr, aid, bid)
        SELECT      @curr,
                    aid,
                    bid
        FROM        @a a
        INNER JOIN  @x x
                ON  a.id = x.aid
        INNER JOIN  @b b
                ON  x.bid = b.id
        WHERE       a.id = @aid
            
        SELECT @curr = @curr + 1
    END
    SELECT  @end_where = CURRENT_TIMESTAMP
    
    SELECT  @count_where = COUNT(1) FROM @temp
    DELETE FROM @temp
    
    SELECT  @curr = 0
    SELECT  @begin_join = CURRENT_TIMESTAMP
    WHILE (@curr &lt; @iter)
    BEGIN
        INSERT      @temp (curr, aid, bid)
        SELECT      @curr,
                    aid,
                    bid
        FROM        @a a
        INNER JOIN  @x x
                ON  a.id = x.aid
                AND a.id = @aid
        INNER JOIN  @b b
                ON  x.bid = b.id
        
        SELECT @curr = @curr + 1
    END
    SELECT  @end_join = CURRENT_TIMESTAMP

    SELECT  @count_join = COUNT(1) FROM @temp
    DELETE FROM @temp

    SELECT  @count_where AS count_where,
            @count_join AS count_join,
            DATEDIFF(millisecond, @begin_where, @end_where) AS elapsed_where,
            DATEDIFF(millisecond, @begin_join, @end_join) AS elapsed_join


Which SQL query is faster? Filter on Join criteria or Where clause?

I have the following two tables:

    Table1
    ----------
    ID   Name
    1    A
    2    B
    3    C

    Table2
    ----------
    ID   Name
    1    Z

I need to insert data from `Table1` to `Table2`. I can use the following syntax:

    INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1

However, in my case, duplicate IDs might exist in `Table2` (in my case, it&#39;s just &quot;`1`&quot;) and I don&#39;t want to copy that again as that would throw an error. 

I can write something like this:

    IF NOT EXISTS(SELECT 1 FROM Table2 WHERE Id=1)
    INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 
    ELSE
    INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 WHERE Table1.Id&lt;&gt;1

Is there a better way to do this without using `IF - ELSE`? I want to avoid two `INSERT INTO-SELECT` statements based on some condition.

Avoid duplicates in INSERT INTO SELECT query in SQL Server

Can anyone tell me if a table in a relational database (such as MySQL / SQL SERVER) can be without a primary key?

For example, I could have table `day_temperature`, where I register `temperature` and `time`. I don&#39;t see the reason to have a primary key for such a table.

Can a database table be without a primary key?

A few examples to show, just incase:

*Inline Table Valued*

    CREATE FUNCTION MyNS.GetUnshippedOrders()
    RETURNS TABLE
    AS 
    RETURN SELECT a.SaleId, a.CustomerID, b.Qty
        FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
            ON a.SaleId = b.SaleId
            INNER JOIN Production.Product c ON b.ProductID = c.ProductID
        WHERE a.ShipDate IS NULL
    GO

*Multi Statement Table Valued*

    CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
    RETURNS @CustomerOrder TABLE
    (SaleOrderID    INT         NOT NULL,
    CustomerID      INT         NOT NULL,
    OrderDate       DATETIME    NOT NULL,
    OrderQty        INT         NOT NULL)
    AS
    BEGIN
        DECLARE @MaxDate DATETIME
       
        SELECT @MaxDate = MAX(OrderDate)
        FROM Sales.SalesOrderHeader
        WHERE CustomerID = @CustomerID
    
        INSERT @CustomerOrder
        SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
        FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
            ON a.SalesOrderID = b.SalesOrderID
            INNER JOIN Production.Product c ON b.ProductID = c.ProductID
        WHERE a.OrderDate = @MaxDate
            AND a.CustomerID = @CustomerID
        RETURN
    END
    GO
       
Is there an advantage to using one type (in-line or multi statement) over the other? Is there certain scenarios when one is better than the other or are the differences purely syntactical? I realise the two example queries are doing different things but is there a reason I would write them in that way?

Reading about them and the advantages/differences haven&#39;t really been explained.

Multi-statement Table Valued Function vs Inline Table Valued Function

I try to use single quotes as much as possible and I&#39;ve noticed that I can&#39;t use \n in single quotes. I know I can just enter a newline literally by pressing return, but that screws up the indentation of my code.

Is there some ASCII character or something that I can type that will produce newline when I&#39;m using single quotes?

Print newline in PHP in single quotes

I would like to be able to get the name of a variable as a string but I don&#39;t know if Python has that much introspection capabilities. Something like:

    &gt;&gt;&gt; print(my_var.__name__)
    &#39;my_var&#39;

I want to do that because I have a bunch of variables I&#39;d like to turn into a dictionary like :

    bar = True
    foo = False
    &gt;&gt;&gt; my_dict = dict(bar=bar, foo=foo)
    &gt;&gt;&gt; print my_dict 
    {&#39;foo&#39;: False, &#39;bar&#39;: True}

But I&#39;d like something more automatic than that.

Python have `locals()` and `vars()`, so I guess there is a way.

Simpler way to create dictionary of separate variables?

In [C#][1], [Ruby][2], and many other languages you can denote a string as to not need escaping.
In C# it’s like this

&lt;!-- language: lang-csharp --&gt;

    string s = @&quot;\whatever\this\is&quot;;

The results are when printed:

    \whatever\this\is

Is this supported in any form in JavaScript?

  [1]: https://en.wikipedia.org/wiki/C_Sharp_%28programming_language%29
  [2]: https://en.wikipedia.org/wiki/Ruby_%28programming_language%29


Does JavaScript have literal strings?

Is there a quick way in Python to replace strings but, instead of starting from the beginning as `replace` does, starting from the end? For example:

    &gt;&gt;&gt; def rreplace(old, new, occurrence)
    &gt;&gt;&gt;     ... # Code to replace the last occurrences of old by new

    &gt;&gt;&gt; &#39;&lt;div&gt;&lt;div&gt;Hello&lt;/div&gt;&lt;/div&gt;&#39;.rreplace(&#39;&lt;/div&gt;&#39;,&#39;&lt;/bad&gt;&#39;,1)
    &gt;&gt;&gt; &#39;&lt;div&gt;&lt;div&gt;Hello&lt;/div&gt;&lt;/bad&gt;&#39;

rreplace - How to replace the last occurrence of an expression in a string?

I&#39;d like to write a method that converts CamelCase into a human-readable name.

Here&#39;s the test case:

    public void testSplitCamelCase() {
        assertEquals(&quot;lowercase&quot;, splitCamelCase(&quot;lowercase&quot;));
        assertEquals(&quot;Class&quot;, splitCamelCase(&quot;Class&quot;));
        assertEquals(&quot;My Class&quot;, splitCamelCase(&quot;MyClass&quot;));
        assertEquals(&quot;HTML&quot;, splitCamelCase(&quot;HTML&quot;));
        assertEquals(&quot;PDF Loader&quot;, splitCamelCase(&quot;PDFLoader&quot;));
        assertEquals(&quot;A String&quot;, splitCamelCase(&quot;AString&quot;));
        assertEquals(&quot;Simple XML Parser&quot;, splitCamelCase(&quot;SimpleXMLParser&quot;));
        assertEquals(&quot;GL 11 Version&quot;, splitCamelCase(&quot;GL11Version&quot;));
    }


How do I convert CamelCase into human-readable names in Java?

How to detect if a string contains at least a number (digit) in SQL server 2005?

How to detect if a string contains at least a number?

Currently we use separate a drop statements for each stored procedure in the script file:  

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N&#39;[dbo].[MySP]&#39;)   
      AND type in (N&#39;P&#39;, N&#39;PC&#39;))  
    DROP PROCEDURE [dbo].[MySP] 

 

Is there a way to drop them all at once, or maybe in a loop?


How to drop all stored procedures at once in SQL Server database?

Say I have a stored procedure consisting of several separate SELECT, INSERT, UPDATE and DELETE statements. There is no explicit BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS logic. 

How will SQL Server handle this stored procedure transaction-wise? Will there be an implicit connection for each statement? Or will there be one transaction for the stored procedure?

Also, how could I have found this out on my own using T-SQL and / or SQL Server Management Studio?

Thanks!


How does SQL Server treat statements inside stored procedures with respect to transactions?

Im trying to get the value from BrandID in one table and add it to another table. But I can&#39;t get it to work. Anybody know how to do it right?

    CREATE PROCEDURE AddBrand
    AS
    
    DECLARE 
    @BrandName nvarchar(50),
    @CategoryID int,
    @BrandID int
    
    SELECT @BrandID = BrandID FROM tblBrand 
    WHERE BrandName = @BrandName

    INSERT INTO tblBrandinCategory (CategoryID, BrandID) 
           VALUES (@CategoryID, @BrandID) 
    
    RETURN

How to declare a variable in SQL Server and use it in the same Stored Procedure

I want to be able create stored procedures using `phpMyAdmin` and later on use it through php. 

### But I dont know how to?

From what I know, I found out that we cannot manage stored procedures through `phpMyAdmin`.

**What other tool can manage stored procedure?**

I am not even sure if it is better option to use stored procedure through PHP. *Any suggestion?*



How to write a stored procedure using phpmyadmin and how to use it through php?

In T-SQL, this is allowed:


    DECLARE @SelectedValue int
    SELECT @SelectedValue = MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1


So, it&#39;s possible to get the value of a SELECT and stuff it in a variable (provided it&#39;s scalar, obviously).

If I put the same select logic in a stored procedure:

    CREATE PROCEDURE GetMyInt
    AS
    SELECT MyIntField FROM MyTable WHERE MyPrimaryKeyField = 1

Can I get the output of this stored procedure and stuff it in a variable?

Something like:

    DECLARE @SelectedValue int
    SELECT @SelectedValue = EXEC GetMyInt

(I know the syntax above is not allowed because I tried it!)

T-SQL get SELECTed value of stored procedure

I was hoping to find an easy way to get a parameter list of a stored procedures parameters. If the procedure has 3 paramaters, I want a list like this:


**param1&lt;br&gt;
param2&lt;br&gt;
param3**

It would be best to be able to do this in C# Code, but SQL would suffice as well. Ideas?



Get stored procedure parameters by either C# or SQL?

Can anyone tell me where is the mistake in the following query

    ALTER TABLE Countries
    ADD ( 
    HasPhotoInReadyStorage  bit,
     HasPhotoInWorkStorage  bit,
     HasPhotoInMaterialStorage bit,
     HasText  bit);
    
    ALTER TABLE Regions
    ADD ( HasPhotoInReadyStorage  bit,
     HasPhotoInWorkStorage  bit,
     HasPhotoInMaterialStorage bit
     HasText  bit);
    
    ALTER TABLE Provinces
    ADD ( HasPhotoInReadyStorage  bit,
     HasPhotoInWorkStorage  bit,
     HasPhotoInMaterialStorage bit
     HasText  bit);
    
    
    ALTER TABLE Cities
    ADD ( HasPhotoInReadyStorage  bit,
     HasPhotoInWorkStorage  bit,
     HasPhotoInMaterialStorage bit
     HasText  bit);
    
    Alter table Hotels
    Add 
    {
     HasPhotoInReadyStorage  bit,
     HasPhotoInWorkStorage  bit,
     HasPhotoInMaterialStorage bit,
     HasHotelPhotoInReadyStorage  bit,
     HasHotelPhotoInWorkStorage  bit,
     HasHotelPhotoInMaterialStorage bit,
     HasReporterData  bit,
     HasMovieInReadyStorage  bit,
     HasMovieInWorkStorage  bit,
     HasMovieInMaterialStorage bit
    };

I get the following errors:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near &#39;(&#39;.
    Msg 102, Level 15, State 1, Line 9
    Incorrect syntax near &#39;(&#39;.
    Msg 102, Level 15, State 1, Line 15
    Incorrect syntax near &#39;(&#39;.
    Msg 102, Level 15, State 1, Line 22
    Incorrect syntax near &#39;(&#39;.
    Msg 102, Level 15, State 1, Line 29
    Incorrect syntax near &#39;{&#39;.

Alter table add multiple columns ms sql

I know I&#39;ve done this before years ago, but I can&#39;t remember the syntax, and I can&#39;t find it anywhere due to pulling up tons of help docs and articles about &quot;bulk imports&quot;.

Here&#39;s what I want to do, but the syntax is not exactly right... please, someone who has done this before, help me out :)

    INSERT INTO dbo.MyTable (ID, Name)
    VALUES (123, &#39;Timmy&#39;),
        (124, &#39;Jonny&#39;),
        (125, &#39;Sally&#39;)

I know that this is *close* to the right syntax. I might need the word &quot;BULK&quot; in there, or something, I can&#39;t remember. Any idea?

I need this for a SQL Server 2005 database. I&#39;ve tried this code, to no avail:

    DECLARE @blah TABLE
    (
    	ID INT NOT NULL PRIMARY KEY,
    	Name VARCHAR(100) NOT NULL
    )
    
    INSERT INTO @blah (ID, Name)
    	VALUES (123, &#39;Timmy&#39;)
    	VALUES (124, &#39;Jonny&#39;)
    	VALUES (125, &#39;Sally&#39;)
    
    SELECT * FROM @blah

I&#39;m getting `Incorrect syntax near the keyword &#39;VALUES&#39;.`

Insert multiple rows WITHOUT repeating the &quot;INSERT INTO ...&quot; part of the statement?

I know this is almost a duplicate of : https://stackoverflow.com/questions/1269706/the-error-login-failed-for-user-nt-authority-iusr-in-asp-net-and-sql-server-2 and https://stackoverflow.com/questions/97594/login-failed-for-user-username-system-data-sqlclient-sqlexception-with-linq-i but some things don&#39;t add up compared to other appliations on my server and I am not sure why.

**Boxes being used:**

Web Box  
SQL Box  
SQL Test Box

**My Application:**

I have an ASP.NET Web Application, which references a class library that uses LINQ-to-SQL. Connection string set up properly in the class library. As per https://stackoverflow.com/questions/97594/login-failed-for-user-username-system-data-sqlclient-sqlexception-with-linq-i I also added this connection string to the Web Application. 

The connection string uses SQL credentials as so (in both web app and class library):

     &lt;add name=&quot;Namespace.My.MySettings.ConnectionStringProduction&quot;
            connectionString=&quot;Data Source=(SQL Test Box);Initial Catalog=(db name);Persist Security Info=True;User ID=ID;Password=Password&quot;
            providerName=&quot;System.Data.SqlClient&quot; /&gt;

This connection confirmed as working via adding it to Server Explorer. This is the connection string my .dbml file is using.

**The problem:**

I get the following error:

    System.Data.SqlClient.SqlException: Login failed for user &#39;DOMAIN\MACHINENAME$&#39;.

Now referencing this https://stackoverflow.com/questions/1269706/the-error-login-failed-for-user-nt-authority-iusr-in-asp-net-and-sql-server-2 it says that&#39;s really the local network service and using any other non-domain name will not work. 

But I am confused because I&#39;ve checked both SQL Box and SQL Test Box SQL Management Studio and both have `NT AUTHORITY/NETWORK SERVICE` under Security -&gt; Logins, at the database level, that isn&#39;t listed under Security -&gt; Users, but at the database level Security -&gt; Users I have the user displayed in the connection string.

At NTFS level on web server, the permissions have NETWORK SERVICE has full control.

The reason why I am confused is because I have many other web applications on my Web Server, that reference databases on both SQL Box and SQL Test Box, and they all work. But I cannot find a difference between them and my current application, other than I am using a class library. Will that matter? Checking NTFS permissions, setup of Security Logins at the server and databases levels, connection string and method of connecting (SQL Server credentials), and IIS application pool and other folder options, are all the same.

Why do these applications work without adding the machinename$ to the permissions of either of my SQL boxes? But that is what the one link is telling me to do to fix this problem.


Login failed for user &#39;DOMAIN\MACHINENAME$&#39;

I am getting the following error. Could you please help me?

&gt; Msg 547, Level 16, State 0, Line 1     
&gt; The INSERT statement conflicted with the FOREIGN KEY constraint &quot;FK_Sup_Item_Sup_Item_Cat&quot;. The conflict occurred in database &quot;dev_bo&quot;, table &quot;dbo.Sup_Item_Cat&quot;. The statement has been terminated.

**Code:**

    insert into sup_item (supplier_id, sup_item_id, name, sup_item_cat_id, 
                          status_code, last_modified_user_id, last_modified_timestamp, client_id)   
    values (10162425, 10, &#39;jaiso&#39;, &#39;123123&#39;,
            &#39;a&#39;, &#39;12&#39;, &#39;2010-12-12&#39;, &#39;1062425&#39;)

The last column `client_id` is causing the error. I tried to put the value which already exists in the `dbo.Sup_Item_Cat` into the column, corresponding to the sup_item.. but no joy :-(

INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

I can see how to turn off row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row level locking and NOT use page level locking?

Content Type	Original Author	Original Content on Stackoverflow
Question	NLV	View Question on Stackoverflow
Solution 1 - Sql Server	Damien_The_Unbeliever	View Answer on Stackoverflow
Solution 2 - Sql Server	Daniel Quinlan	View Answer on Stackoverflow

Check if a string contains a substring in SQL Server 2005, using a stored procedure

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

How to Pass Parameters to Activator.CreateInstance<T>()

Checking for a dirty index or untracked files with Git

Attributions