Consider the following example:  CREATE TEMPORARY TABLE testing (     names TEXT[] );  INSERT INTO testing VALUES     ('{toplevel1}'),     ('{toplevel1.sub1, toplevel2.sub2}'),     ('{toplevel1.sub1.sub2, toplevel2.sub2}'),     ('{toplevel1.sub1.sub3, toplevel2.sub2}'),     ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),     ('{toplevel2.sub1}') ;  SELECT * FROM testing WHERE 'toplevel1.%' ILIKE ANY(names);   I want to write a select where "any" element of an array matches a pattern. But I can't figure out a way to do it. In the example I've given, using the ILIKE operator can't work because it checks if the RHS matches the LHS. For this case I would need a similar operator but which checks if the LHS matches the RHS.  update: I'm currently fiddling around with something like this without much success:  SELECT * FROM testing WHERE names ILIKE ANY (ARRAY(SELECT unnest(names) || '%'));
123924
I'm trying to match a string pattern to any string in an array:  SELECT 'abc' LIKE ANY('{"abc","def"}') -- TRUE  -- BUT  SELECT 'ab%' LIKE ANY('{"abc","def"}') -- FALSE, I expect TRUE   What is wrong with the second query?
6749
What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.  Consider the following SQL statement:  SELECT my_column FROM my_table where search_column IN (?)   Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.    What workarounds are available?
98204
During my work with databases I noticed that I write query strings and in this strings I have to put several restrictions in the where-clause from a list/array/collection. Should look like this:  select * from customer  where customer.id in (34, 26, ..., 2);   You can simplify this by reducing this to the question that you have collection of strings and want to create a comma-separated list of this strings in just one string.  My approach I have used so far is something like that:  String result = ""; boolean first = true; for(String string : collectionOfStrings) {     if(first) {         result+=string;         first=false;     } else {         result+=","+string;     } }   But this is as you can see very ugly. You cannot see what happens there on the first look, especially when the constructed strings (like every SQL query) is getting complicated.  What is your (more) elegant way?
73199
I have a need to replicate SOQL's LIKE behaviour in Apex:  The % wildcard matches zero or more characters The _ wildcard matches exactly one character Case insensitive  including for cases where characters that have special meaning in regular expressions are used e.g. ( and ). What code do I need?
10807
maybe there is a simple answer to my problem:  I have a mssql table ('Person') with a field for first and one for last name. I am coding a Program, where the User should be able to search for persons.     SELECT id, firstname, lastname FROM Person WHERE firstname LIKE   @searchstring + '%' OR lastname LIKE @searchstring + '%'   doesn't work very well, because if the user types 'John W' nothing is found.  For example the person I search for is called John Williams: It should work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'  Concatenating first and lastname and use %Searchstring% also doesn't work well, because if the searchstring is e.g. 'Will' it should find people who are called Will, Willibald, Williams or Willson or something like that, but not a Person called Goodwill e.g.  And the query should be as fast as possible, because it is executed at every keydown...  edit: forgot to mention that the database must not be changed, because it is from another company.  Thank you for your help.
122930
I'm comparing two list given in the list to see if the item.name exists in both for loop. If not then add into a list to show the error for the missing names.  set&lt;id&gt; setAssetIds = new set&lt;id&gt;(); list&lt;Asset_Store__c&gt;  assets = [select id,name......]; for(Asset_Store__c item : assets ) {     setAssetIds.add(item.id);  }   list&lt;Meter__c&gt;  meters = [select id,name......where id in : setAssetIds];  //now comparing the two lists for(Asset_Store__c a : assets) {    for(Meter__c m : meters)    {      if(a.Name != m.Name)      {        //not matched...        break;      }     } }   the problem with the above approach is that, its not finding the correct name.
78923
I'm dealing with nullable values in a column of a table, i'm wondering why this:  with cte as (     select 'c1'::text as c1,null::text as c2     union     select 'c2'::text as c1,null::text as c2 ) select * from cte where c2 is null   returns a result set with the two "rows" having a null value in c2; comparting to this query:  with cte as (     select 'c1'::text as c1,null::text as c2     union     select 'c2'::text as c1,null::text as c2 ) select * from cte where c2 = null   that returns an empty result set!!  so it looks like that field is null is actually different from field = null? the question is... is there "null-compatible operator" to use? so i can write the comparision like: column_name = $1 and argument $1 can be a not null or null value. IF $1 is a null value then it will semantically equals to write as IS NULL, ELSE (if $1 is not a null value) then it would be semantically equals to = $1.  P.D: This question also applies to the case is NOT NULL versus &lt;&gt; NULL
108409
When I write a query like this...  select * from table1 t1 join table2 t2 on t1.id = t2.id   Does the SQL optimizer, not sure if that is the correct term, translate that to...  select * from table1 t1, table2 t2 where t1.id = t2.id   Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?  Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.
56441
I am currently programming a trigger on Opportunities.   I created a test class for this and in my Sandbox I created an account, contact and opportunity. When I try to reference them   Opportunity Opp = [SELECT id from Opportunity where name = 'Test1'];    and then start my   Test.startTest();         Opp.StageName = 'PO in (100%)';          update Opp;         Test.stopTest();   It just errors and shows that Opp returns 0 rows. However when I paste my first bit of code in 'Execute Anonymous' it returns a row and the correct info.   Does this mean I have to create an account, contact, quote and all necessary fields in my test class?
63838
Let's say you have a few expressions like the following:  $$\begin{array}((x+17)^2 \\ x^2 + 34x + 289 \end{array} \\ 288 + \frac{x^2}{2} + \frac{x^2}{2} + 34x + 1 \\ [...] $$  You get the idea: there's an infinite number of ways of representing the same expression. The challenge is that my application is validating answers to math problems, input by math students practicing a certain concept. I'd like to make sure that whenever they submit an answer, the checker is completely insensitive to the issues of ordering, simplification, formatting etc. which would be plain frustrating. I'd also ideally like to support more than one variable such as x, y, z in the same expression.  What's a reliable way to check that two expressions are the same? The most brute force way I can think of would be to just plug a number into each variable of each expression and see what the result is.  I have a hunch however that there might be ways of getting false positives this way. That, and I might be either missing something really obvious about this, or there might be a better way of doing the comparison.  Would love to hear what you think.
115908
Suppose the following function:  CREATE FUNCTION [dbo].[ufnTest]() RETURNS TABLE AS RETURN SELECT 1 AS Nr   My actual function will select actual data from many different tables. I (mostly) understand the risks of using nolock hints, and decided that in this case I actually want them. So I try to call above function like this:  SELECT * FROM [dbo].[ufnTest]() WITH(NOLOCK)   However,  with the following message:     Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.   The tips for fixing this error are not relevant. Perhaps this table hint isn't available for table valued functions then?  There are several alternatives I'd consider. One is to use the hint on all tables in my select query, but I'd prefer not to as (a) I'll have to remember this whenever I change the function around and (b) it's repetitive. Another alternative would be to SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, but a downside is that I don't quite know how to set it back to wat it was before (and statements after the select from my TVF should have the original isolation level).  So, I'd prefer to make WITH (NOLOCK) for a select from a table valued function work. Is this possible?
6834
Given a number, how do I discover in what table and column it could be found within? I don't care if it's fast, it just needs to work.
53082
To illustrate, assume that I have two tables as follows:  VehicleID Name 1         Chuck 2         Larry  LocationID VehicleID City 1          1         New York 2          1         Seattle 3          1         Vancouver 4          2         Los Angeles 5          2         Houston   I want to write a query to return the following results:  VehicleID Name    Locations 1         Chuck   New York, Seattle, Vancouver 2         Larry   Los Angeles, Houston   I know that this can be done using server side cursors, ie:  DECLARE @VehicleID int DECLARE @VehicleName varchar(100) DECLARE @LocationCity varchar(100) DECLARE @Locations varchar(4000) DECLARE @Results TABLE (   VehicleID int   Name varchar(100)   Locations varchar(4000) )  DECLARE VehiclesCursor CURSOR FOR SELECT   [VehicleID] , [Name] FROM [Vehicles]  OPEN VehiclesCursor  FETCH NEXT FROM VehiclesCursor INTO   @VehicleID , @VehicleName WHILE @@FETCH_STATUS = 0 BEGIN    SET @Locations = ''    DECLARE LocationsCursor CURSOR FOR   SELECT     [City]   FROM [Locations]   WHERE [VehicleID] = @VehicleID    OPEN LocationsCursor    FETCH NEXT FROM LocationsCursor INTO     @LocationCity   WHILE @@FETCH_STATUS = 0   BEGIN     SET @Locations = @Locations + @LocationCity      FETCH NEXT FROM LocationsCursor INTO       @LocationCity   END   CLOSE LocationsCursor   DEALLOCATE LocationsCursor    INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations  END      CLOSE VehiclesCursor DEALLOCATE VehiclesCursor  SELECT * FROM @Results   However, as you can see, this requires a great deal of code.  What I would like is a generic function that would allow me to do something like this:  SELECT VehicleID      , Name      , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations FROM Vehicles   Is this possible?  Or something similar?
32343
I'm trying to get a handle on if there's a good time to use standard linq keywords or linq extension methods with lambda expressions.  They seems to do the same thing, just are written differently.  Is it purely a matter of style?  var query = from p in Products     where p.Name.Contains("foo")     orderby c.Name     select p;  // or with extension methods: var query = Products     .Where(p =&gt; p.Name.Contains("foo"))     .OrderBy(p =&gt; p.Name);   They're very similar with the second example being a bit more terse, but perhaps less expressive if you don't know what the => is doing.  Other than writing terse code, are there other advantages to using the extension methods as opposed to the LINQ syntax?
23755
Generally, when using the conditional operator, here's the syntax:  int x = 6; int y = x == 6 ? 5 : 9;   Nothing fancy, pretty straight forward.  Now, let's try to use this when assigning a Lambda to a Func type. Let me explain:  Func&lt;Order, bool&gt; predicate = id == null     ? p =&gt; p.EmployeeID == null     : p =&gt; p.EmployeeID == id;   That's the same syntax, and should work? Right? For some reason that doesn't. The compiler gives this nice cryptic message:     Error  1   Type of conditional expression cannot be determined because there is no implicit conversion between 'lambda expression' and 'lambda expression'   I then went ahead and changed the syntax and this way it did work:  Func&lt;Order, bool&gt; predicate = id == null     ? predicate = p =&gt; p.EmployeeID == null     : predicate = p =&gt; p.EmployeeID == id;   I'm just curious as to why it doesn't work the first way?  (Side note: I ended up not needing this code, as I found out that when comparing an int value against null, you just use object.Equals)
56382
I’m using the listings package for code listings. Now I try to highlight some part of the code using \hl from the soul package.  Here’s an example of a resulting query where the injected part of a SQL injection should be highlighted:  \begin{lstlisting}[escapechar=@,language=SQL] SELECT name, password FROM users WHERE name='@\hl{' UNION SELECT "10", 1 \#}@'; \end{lstlisting}   But inside the highlighted part the SQL syntax highlighting is not applied.  So I tried to use \lstlinline for the code part inside \hl as well but it didn’t work:  \begin{lstlisting}[escapechar=@,language=SQL] SELECT name, password FROM users WHERE name='@\hl{\lstinline[language=SQL]$' UNION SELECT "10", 1 #$}@'; \end{lstlisting}   Although this \lstinline part works outside the lstlisting block, it doesn’t work inside it. The error I get for that particular line is:     Extra }, or forgotten \endgroup.   Maybe I’m just missing just some special syntax to get this working. Or is it not possible at all to get proper syntax highlighting for the \hl-highlighted part as well?
97409
I need to perform a string comparison (string equality). If the macro is non-expandable (e.g. contains \def or \let), it causes problems in the place where it is used.  I tried using \ifthenelse from the package with the same name, but it would seem it is not expandable (the same problems as when I use \def in the macro occur).  \ifthenelse{\equal{a}{b}}{if-case}{else-case}   How can I test the equality of two strings (#1 and a constant, for that matter) in an expandable way?