title: Concat is not working in SQL Server
body: <p>I have table called product, which contains name(nvarchar 1000), </p>
  
  <pre><code>P1
  P2
  P3
  P4
  </code></pre>
  
  <p>I have a account table named as accnt_test with product_name.</p>
  
  <pre><code>id   name
  A1   P1;;p2;;p3;;p4
  A2   P1;;p2
  A3   P1;;p2;;p3
  A4   P1;;p2;;p3;;p4
  A5   P1
  </code></pre>
  
  <p>I have to pick those accounts from account table which contains all the products from product table.</p>
  
  <p>so my output should look like:</p>
  
  <pre><code>id  name
  A1  P1;;p2;;p3;;p4
  A4  P1;;p2;;p3;;p4
  </code></pre>
  
  <p>because these two accounts contains all the products from product table.</p>
  
  <p>I have written a query in SQL Server but it is not working.</p>
  
  <p>This is query:</p>
  
  <pre><code>SELECT *
    FROM accnt_test ACC
   WHERE ACC.product_name IN
         (SELECT VM.product_name
            FROM (SELECT A.*, P.NAME
                    FROM accnt_test A, product P
                   WHERE A.product_name LIKE
                         CONCAT('%', CONCAT(P.NAME, '%'))) VM
           GROUP BY VM.product_name
          HAVING COUNT(*) = (SELECT COUNT(DISTINCT NAME) FROM PRODUCT));
  </code></pre>
  
  <p>so how can it will be used in case of concat .</p>
  
  <p>but SQL Server showing an error:</p>
  
  <pre><code>sg 195, Level 15, State 10, Line 9
  'CONCAT' is not a recognized built-in function name.
   Msg 102, Level 15, State 1, Line 11
  Incorrect syntax near ')'.
  </code></pre>