no script to attach as I can pull the data just fine, I am struggling to join my two tables though. The column I want to join on has the same information formatted differently in each table, making it worse, one of these columns uses wildcards for the program that takes data from there. Example of what I mean: T1.Col1 would contain the actual value, say EXAMPLE   A , EXAMPLE   B , EXAMPLF A T2.Col1 would have one entry like EXAMPL*[AB] The Program using that table would treat all values the same if it started in EXAMPL, and ending in EITHER A or B. Meaning the following would all be a match, this is what I need to recreate for my script: EXAMPLE A , EXAMPLE B , EXAMPLF A Can I recreate that logic in SQL? To find a match/closest match based on certain conditions? T2 Column contains about 200 values just like that and all different, so I need a solution that can apply to everything and not just this case. I've tried formatting T2 values using Replace to be more like T1 but the square brackets are killing me. Making new rows in the output table might help but we're at the limit of what I can do in SQL.
124092
OK, so I have a Microsoft SQL Server 2014 database table called owner with around 90,000 records with owner information, another called vehicle with vehicle information  Owner_Name                   owner_id       V_name     owner_id    exempt -------------------------------------       ------------------------------ JACOB JAMISON &amp; JESSICA           35        Civic            35        H3 JACOB JAMISON M &amp; JESSICA B       39        Accord           39        H3  BLACKSON BARRINGTON               56        Bugatti          56        H6 BLACKSON BARRINGTON H             98        SSC              98        H7 BRUSTER MICHAEL                   107       Corvette         107       H9   I'm trying to find all records that have more than one exemption on a vehicle     ( H0 means no exemption). This code below has worked well, as long as the name is exactly the same. However, if there's a variation, such as an extra letter or if it's entered backwards, then it won't return those records. I've looked at things like SOUNDEX, but this wouldn't work in my scenario.  SELECT Owner_name      , COUNT(Owner_name) AS 'xNameAppears'      , COUNT(v.exempt) AS 'ExemptionCount'  FROM owner o INNER JOIN vehicle V ON V.owner_id = o.owner_id WHERE v.exempt &lt;&gt; 'H0' GROUP BY O.owner_name HAVING COUNT(v.exempt) &gt; 1   Is there a solution that would allow me to return records like so, not knowing which owner_name's may be similar? Basically trying to get the server to search through the owner_name column and if there's a similarity such as  JACOB JAMISON &amp; JESSICA and JACOB JAMISON M &amp; JESSICA B then it'll return those records like so:  Owner_Name                      xNameAppears      ExemptCount -------------------------------------------------------------       JACOB JAMISON &amp; JESSICA           2                         2 JACOB JAMISON M &amp; JESSICA B       2                         2 BLACKSON BARRINGTON               2                         2 BLACKSON BARRINGTON H             2                         2   Thank you in advance!
114942
I'm trying to combine two different files, one called itemnum and another called items.   itemnum contains:  ItemNumber 1201 1221 1320 1340 1410   and items contains:  Item Lobby Furniture Ballroom Specialties Poolside Carts Formal Dining Specials Reservation Logs   I want to use join here right? So that the output looks like this:  ItemNumber:Item 1201:Lobby Furniture 1221:Ballroom Specialties 1320:Poolside Carts 1340:Formal Dining Specials 1410:Reservation Logs   I can't even figure out how to get them to join, let alone add the :  I tried join itemnum items &gt; prodinfo, but that just gives me an empty file.
74148
Does anyone know of a program, a utility, or some programmatic library, preferably for Linux, that takes an unformatted SQL string and pretty prints it?  For example I would like the following  select * from users where name = 'Paul'   be changed to something like this  select *  from users where    name = 'Paul'   The exact formatting is not important. I just need something to take a large SQL string and break it up into something more readable.
119344
File1.txt      id                            No     gi|371443199|gb|JH556661.1| 7907290     gi|371443198|gb|JH556662.1| 7573913     gi|371443197|gb|JH556663.1| 7384412     gi|371440577|gb|JH559283.1| 6931777   File2.txt   id                              P       R       S  gi|367088741|gb|AGAJ01056324.1| 5       5       0  gi|371443198|gb|JH556662.1|     2       2       0  gi|367090281|gb|AGAJ01054784.1| 4       4       0  gi|371440577|gb|JH559283.1|     21      19      2   output.txt   id                              P       R       S  NO  gi|371443198|gb|JH556662.1|     2       2       0  7573913  gi|371440577|gb|JH559283.1|     21      19      2  6931777   File1.txt has two columns &amp; File2.txt has four columns. I want to join both files which has unique id (array[1] should match in both files (file1.txt &amp; file2.txt) and give ouput only matched id (see output.txt).  I have tried join -v &lt;(sort file1.txt) &lt;(sort file2.txt). Any help with awk or join commands requested.
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?
72854
I am trying to align the columns of multiple tables.  I know I can use p column format to fix column widths but I would prefer that TeX automatically figure out the widths for me.  I was hoping to:    output one table with all rows of each subsequent table so that the column widths of the combined table would be automatically good for all the subsequent tables  then output each subsequent table using the same settings and  figure out some way in which the first combined table could be generated but not visible.   Here is my attempt.  The first combined table uses \savetabu to save the settings and then each subsequent table uses \usetabu to retrieve them.  Unfortunately this does not work as I had hoped.  The columns do not line up as the output below (after the code) shows. Does anyone have a method of accomplishing this?  \documentclass{article}  \usepackage{tabu} \begin{document}  Output both tables together as one table and then output each individual table.  We had hoped that this would cause the subsequent tables to be the same width as the first since the first contains all columns of the subsequent tables but it seems that that does not work.  \begin{center} \begin{tabu}{ll}\savetabu{mytabu2} 1 &amp; 2 \\ 3 &amp; 4 \\ abcdefghijklmnopqrstuvwxyz &amp; 10 \\ 5 &amp; 6 \\ \end{tabu} \end{center}  \begin{center} \begin{tabu}{\usetabu{mytabu2}} 1 &amp; 2 \\ 3 &amp; 4 \\ \end{tabu} \end{center}  \begin{center} \begin{tabu}{\usetabu{mytabu2}} abcdefghijklmnopqrstuvwxyz &amp; 10 \\ 5 &amp; 6 \\ \end{tabu} \end{center}  \end{document}   Here is the output:
89240
How can I add another 2 values after matching the values from 2 columns in another file just like VLOOKUP?  Sample below. Value in column 6 &amp; 7 from file1, when matched with column 1 &amp; 2 from file2, will add column 9 &amp; 10 in file1 with the value of column 3 &amp; 4 from file2.  file1.txt  1 1 1 1 1 5 9 1  2 2 2 2 2 7 8 2  3 3 3 3 3 7 7 3  4 4 4 4 4 8 6 4   file2.txt  5 9 A B  8 6 E F  7 7 G H  7 8 C D   output.txt  1 1 1 1 1 5 9 1 A B  2 2 2 2 2 7 8 2 C D  3 3 3 3 3 7 7 3 G H  4 4 4 4 4 8 6 4 E F   Thanks,
29248
Background  I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in ).  Question  Is there a way that I can incorporate this select statement into a script that dumps the queried data into a new database, such that the database could be installed on a new mysql server, and the statement would work with the new database. The new database should not contain records in addition to those that have been used in the query.  Update: For clarification, I am not interested in a csv dump of query results. What I need to be able to do is to dump the database subset so that it can be installed on another machine, and then the query itself can be reproducible (and modifiable with respect to the same dataset).   Example  For example, my analysis might query a subset of data that requires records from multiple (in this example 3) tables:  select table1.id, table1.level, table2.name, table2.level         from table1 join table2 on table1.id = table2.table1_id         join table3 on table3.id = table2.table3_id        where table3.name in ('fee', 'fi', 'fo', 'fum');
98776
Is there a way to display the unique values in a column ONLY?  Example:   AAPL AAPL AAPL AAPL AKAM AMZN AMZN AMZN AMZN AMZN AMZN  Expected result:   AAPL AKAM AMZN  I have already done a sort for multiple factors, and the first match for each is the best match.  I'm not sure if this is a vlookup or something else.
44222
I would like to search an entire MS SQL 2000 database for one value. This would be to aid development only. Keep that in mind when considering this question.   This will get all the table names and the column of the data type I'm looking for:  SELECT Columns.COLUMN_NAME, tables.TABLE_NAME FROM INFORMATION_SCHEMA.Columns as Columns  JOIN INFORMATION_SCHEMA.TABLES as tables  On Columns.TABLE_NAME = tables.TABLE_NAME WHERE Columns.DATA_TYPE = 'INT'     I was thinking something like this:  -- Vars DECLARE @COUNTER INT DECLARE @TOTAL INT DECLARE @TABLE CHAR(128) DECLARE @COLUMN CHAR(128) DECLARE @COLUMNTYPE CHAR(128) DECLARE @COLUMNVALUE INT  -- What we are looking for SET @COLUMNTYPE = 'INT' SET @COLUMNVALUE = 3 SET @COUNTER = 0  -- Find out how many possible columns exist SELECT @TOTAL = COUNT(*)   FROM INFORMATION_SCHEMA.Columns as Columns  JOIN INFORMATION_SCHEMA.TABLES as tables  On Columns.TABLE_NAME = tables.TABLE_NAME WHERE Columns.DATA_TYPE = @COLUMNTYPE PRINT CAST(@TOTAL AS CHAR) + 'possible columns' WHILE @COUNTER &lt; @TOTAL BEGIN     SET @COUNTER = @COUNTER +1     -- ADD MAGIC HERE END   Any ideas?  UPDATE I recently found this  that works quite well.
37749
I am working on a project involving cards from the Magic: The Gathering game. To be of any use, I must load the basic information about each card into my database, but some of the fields may contain arrays of text, that is, they may contain multiple values .  For example, a card can be classified to be both "green" and "blue". Or it can have several different creature types specified, and so on.  My first thought was to simply store the values as a string, and then search using the LIKE keyword. In combination with the NOT keyword, it should be possible to search for any desired combination of words, and also exclude certain words.   However, LIKE is going to be (relatively) slow as it can't use an index and has to rely on text pattern matching, and probably has other drawbacks as well. With normalization I could improve speed by assigning an integer key to each keyword, and then create a table that connects each card with the relevant keys (see  for a clearer description).  But with normalized tables I have to deals with joins (severely increased code complexity, not only in the SQL part), transactions (to update all relevant tables or rollback everything), and possibly decreased performance due to joins (see ).  AFAIK there are roughly 20000-25000 different magic cards in existence (and more being released every year). With one row for each card, is it worth it overall to normalize the data, or is the benefit not worth the costs? Why/Why not? And how do you decide this question in general?  EDIT: I am currently using a MySQL database, but I would also appreciate answers that apply to any of the other non-commercial/open source databases out there. Who knows - I might switch to a different database at some point.  Answers should not provide a technical solution only, but explain why it would/might be the right solution in terms of best/good enough performance for least amount of effort.
57994
I've got data in SQL Server 2005 that contains HTML tags and I'd like to strip all that out, leaving just the text between the tags. Ideally also replacing things like &amp;lt; with &lt;, etc. Is there an easy way to do this or has someone already got some sample T-SQL code? I don't have the ability to add extended stored procs and the like, so would prefer a pure T-SQL approach (preferably one backwards compatible with SQL 2000). I just want to retrieve the data with stripped out HTML, not update it, so ideally it would be written as a user-defined function, to make for easy reuse. So for example converting this: &lt;B&gt;Some useful text&lt;/B&gt;&amp;nbsp; &lt;A onclick=&quot;return openInfo(this)&quot;    href=&quot;http://there.com/3ce984e88d0531bac5349&quot;    target=globalhelp&gt;    &lt;IMG title=&quot;Source Description&quot; height=15 alt=&quot;Source Description&quot;          src=&quot;/ri/new_info.gif&quot; width=15 align=top border=0&gt; &lt;/A&gt;&amp;gt;&amp;nbsp;&lt;b&gt;more text&lt;/b&gt;&lt;/TD&gt;&lt;/TR&gt;  to this: Some useful text &gt; more text
58239
I have the following query:  SELECT c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS us USING(userid) WHERE j.jobid = 123;   I have the following questions:   Is the USING syntax synonymous with ON syntax? Are these joins evaluated left to right?  In other words, does this query say: x = companies JOIN users; y = x JOIN jobs; z = y JOIN useraccounts; If the answer to question 2 is yes, is it safe to assume that the companies table has companyid, userid and jobid columns? I don't understand how the WHERE clause can be used to pick rows on the companies table when it is referring to the alias "j"   Any help would be appreciated!
80631
What do the brackets do in a sql statement?  For example, in the statement:    insert into table1 ([columnname1], columnname2) values (val1, val2)  Also, what does it do if the table name is in brackets?
117174
Problem:  Ajax suggest-search on [n] ingredients in recipes. That is: match recipes against multiple ingredients.  For instance: SELECT Recipes using "flower", "salt" would produce: "Pizza", "Bread", "Saltwater" and so forth.  Tables:  Ingredients [     IngredientsID INT [PK],     IngredientsName VARCHAR ]  Recipes [     RecipesID INT [PK],     RecipesName VARCHAR ]  IngredientsRecipes [     IngredientsRecipesID INT [PK],     IngredientsID INT,     RecipesID INT ]   Query:  SELECT     Recipes.RecipesID,     Recipes.RecipesName,     Ingredients.IngredientsID,     Ingredients.IngredientsName FROM     IngredientsRecipes      INNER JOIN Ingredients     ON IngredientsRecipes.IngredientsID = Ingredients.IngredientsID      INNER JOIN Recipes     ON IngredientsRecipes.RecipesID = Recipes.RecipesID WHERE     Ingredients.IngredientsName IN ('salt', 'water', 'flower')   I am currently constructing my query using ASP.NET C# because of the dynamic nature of the WHERE clause.  I bites that I have to construct the query in my code-layer instead of using a stored procedure/pure SQL, which in theory should be much faster.  Have you guys got any thoughts on how I would move all of the logic from my code-layer to pure SQL, or at least how I can optimize the performance of what I'm doing?  I am thinking along the lines of temporary tables:  Step one: SELECT IngredientsID FROM Ingredients and INSERT INTO temp-table  Step two: SELECT RecipesName FROM Recipes joined with IngredientsRecipes joined with temp-table.IngredientsID
81760
Do any queries exist that require RIGHT JOIN, or can they always be re-written with LEFT JOIN?  And more specifically, how do you re-write this one without the right join (and I guess implicitly without any subqueries or other fanciness):   SELECT * FROM t1 LEFT JOIN t2 ON t1.k2 = t2.k2 RIGHT JOIN t3 ON t3.k3 = t2.k3
7026
The normal JOIN ... ON ... syntax is well known. But it is also possible to position the ON clause separately from the JOIN that it corresponds to. This is something that is rarely seen in practice, not found in tutorials and I have not found any web resource that even mentions that this is possible.  Here is a script to play around with:  SELECT * INTO #widgets1 FROM (VALUES (1), (2), (3)) x(WidgetID)   SELECT * INTO #widgets2 FROM (VALUES (1, 'SomeValue1'), (2, 'SomeValue2'), (3, 'SomeValue3')) x(WidgetID, SomeValue)  SELECT * INTO #widgetProperties FROM (VALUES     (1, 'a'), (1, 'b'),     (2, 'a'), (2, 'b')) x(WidgetID, PropertyName)   --q1 SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN #widgets2 w2 ON w2.WidgetID = w1.WidgetID LEFT JOIN #widgetProperties wp ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b' ORDER BY w1.WidgetID   --q2 SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN #widgets2 w2 --no ON clause here JOIN #widgetProperties wp  ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b'  ON w2.WidgetID = w1.WidgetID ORDER BY w1.WidgetID   --q3 SELECT w1.WidgetID, w2.SomeValue, wp.PropertyName FROM #widgets1 w1 LEFT JOIN (     #widgets2 w2 --no SELECT or FROM here     JOIN #widgetProperties wp     ON w2.WidgetID = wp.WidgetID AND wp.PropertyName = 'b') ON w2.WidgetID = w1.WidgetID ORDER BY w1.WidgetID   q1 looks normal. q2 and q3 have these unusual positionings of the ON clause.  This script does not necessarily make much sense. It was hard for me to contrive a meaningful scenario.  So what do these unusual syntax patterns mean? How is this defined? I noticed that not all positions and orderings for the two ON clauses are allowed. What are the rules governing this?  Also is it ever a good idea to write queries like this?