you can either

 - Press &lt;kbd&gt;Ctrl&lt;/kbd&gt;+&lt;kbd&gt;Shift&lt;/kbd&gt;+&lt;kbd&gt;R&lt;/kbd&gt; 

OR

 - Go to Edit &gt;&gt; IntelliSense &gt;&gt; Refresh Local
   Cache

What I am striving to complete is a program which reads in a file and will compare each sentence according to the original sentence. The sentence which is a perfect match to the original will receive a score of 1 and a sentence which is the total opposite will receive a 0. All other fuzzy sentences will receive a grade in between 1 and 0. 

I am unsure which operation to use to allow me to complete this in Python 3. 

I have included the sample text in which the Text 1 is the original and the other preceding strings are the comparisons.  

Text: Sample 
-
Text 1: It was a dark and stormy night. I was all alone sitting on a red chair. I was not completely alone as I had three cats.

Text 20: It was a murky and stormy night. I was all alone sitting on a crimson chair. I was not completely alone as I had three felines
// Should score high point but not 1

Text 21: It was a murky and tempestuous night. I was all alone sitting on a crimson cathedra. I was not completely alone as I had three felines
// Should score lower than text 20

Text 22: I was all alone sitting on a crimson cathedra. I was not completely alone as I had three felines. It was a murky and tempestuous night.
// Should score lower than text 21 but NOT 0

Text 24: It was a dark and stormy night. I was not alone. I was not sitting on a red chair. I had three cats.
// Should score a 0!


Fuzzy String Comparison

I am currently trying to dig deeper into the specification of the Java Virtual Machine. I have been reading [Inside the JVM book online][1] and there is one confusing abstraction I can&#39;t seem to grasp: Constant Pool. here is the excerpt from the book:

&gt; For each type it loads, a Java virtual machine must store a constant pool. A constant pool is an ordered set of constants used by the type, including literals (string, integer, and floating point constants) and symbolic references to types, fields, and methods. Entries in the constant pool are referenced by index, much like the elements of an array. Because it holds symbolic references to all types, fields, and methods used by a type, the constant pool plays a central role in the dynamic linking of Java programs

I have several questions about the above and CP in general:

1. Is CP located in `.class` file for each type?
2. What does the author mean by &quot;symbolic reference&quot;?
3. What is the Constant Pool&#39;s purpose, in simple English?

  [1]: http://www.artima.com/insidejvm/ed2/index.html

What is the purpose of the Java Constant Pool?

I just added an identity column to an existing table with data through the `SSMS` Designer, the table updates fine and I can run a select query against it successfully, but after the query finishes I noticed that the new column is underlined in red with an error indicating it&#39;s an invalid column name.  

Why does this occur?  I would expect the query to fail if the column was invalid.  

Does the SSMS keep some type of internal reference to the old table that must be flushed?  

I tried closing the DB connection and reopening it, but still the same error.  Not sure what I&#39;m missing here.

    SELECT TOP 100 
    [PRIMARY_NDX_Col1],
    [NEW_Col], --QUERY EXECUTES SUCCESSFULLY, BUT THIS IS UNDERLINED RED AS AN INVALID COLUMN
    [Col3]
    FROM [dbo].[MyTable]

SQL Server Invalid Column name after adding new column

I just added an identity column to an existing table with data through the <code>SSMS</code> Designer, the table updates fine and I can run a select query against it successfully, but after the query finishes I noticed that the new column is underlined in red with an error indicating it's an invalid column name.
Why does this occur? I would expect the query to fail if the column was invalid.
Does the SSMS keep some type of internal reference to the old table that must be flushed?
I tried closing the DB connection and reopening it, but still the same error. Not sure what I'm missing here.
<pre><code class="hljs language-css">SELECT TOP 100 
[PRIMARY_NDX_Col1],
[NEW_Col], --QUERY EXECUTES SUCCESSFULLY, BUT THIS IS UNDERLINED RED AS AN INVALID COLUMN
[Col3]
FROM [dbo].[MyTable]
</code></pre>

I have following XML stored in a XML column (called `Roles`) in a SQL Server database.

    &lt;root&gt;
       &lt;role&gt;Alpha&lt;/role&gt;
       &lt;role&gt;Beta&lt;/role&gt;
       &lt;role&gt;Gamma&lt;/role&gt;
    &lt;/root&gt;

I&#39;d like to list all rows that have a specific role in them. This role passed by parameter.

How can I query a value in SQL Server XML column

There are two columns in a MySQL table: `SUBJECT` and `YEAR`. 

I want to generate an alphanumeric unique number which holds the concatenated data from SUBJECT and YEAR.

How can I do this?  Is it possible to use a simple operator like `+`?

MySQL, Concatenate two columns

I am referring to this `stackoverflow` answer:

https://stackoverflow.com/questions/1564956/how-can-i-select-from-list-of-values-in-sql-server/7285095#7285095

How could something similar be done in **Oracle**?

I&#39;ve seen the other answers on this page that use `UNION` and although this method technically works, it&#39;s not what I would like to use in my case.

So I would like to stay with syntax that more or less looks like a comma-separated list of values.

**UPDATE** *regarding the `create type table` [answer][1]*:

I have a table:

    CREATE TABLE BOOK
    (	&quot;BOOK_ID&quot; NUMBER(38,0)
    )

I use this script but it does not insert any rows to the `BOOK` table:

    create type number_tab is table of number;
    
    INSERT INTO BOOK (
        BOOK_ID
    )
    SELECT A.NOTEBOOK_ID FROM
        (select column_value AS NOTEBOOK_ID from table (number_tab(1,2,3,4,5,6))) A
    ;

Script output:

    TYPE number_tab compiled
    Warning: execution completed with warning

But if I use this script it does insert new rows to the `BOOK` table:

    INSERT INTO BOOK (
        BOOK_ID
    )
    SELECT A.NOTEBOOK_ID FROM
        (SELECT (LEVEL-1)+1 AS NOTEBOOK_ID FROM DUAL CONNECT BY LEVEL&lt;=6) A
    ;


  [1]: https://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle/10354083#10354083

How can I select from list of values in Oracle

I&#39;m trying to clear one column for all records in my table.
For example, if my table had three columns: `id`, `comment`, and `likes` - I would like to be able to clear the `likes` column.

    +---+-------+-----+
    |id |comment|likes|
    +-----------------+
    |1  |hi     |3    |
    |2  |hello  |12   |
    |3  |hey    |1    |
    +---+-------+-----+

so that afterwards it would look like this:

    +---+-------+-----+
    |id |comment|likes|
    +-----------------+
    |1  |hi     |     |
    |2  |hello  |     |
    |3  |hey    |     |
    +---+-------+-----+

I&#39;m guessing I would have to use MySQL `UPDATE` to clear the `likes` value, but how do I iterate through all records and keep the `id` and `comment` fields the same?

I don&#39;t want to change each record manually.

Setting value for one column of all records in table

How to return a boolean value on SQL Select Statement?

I tried this code:

    SELECT CAST(1 AS BIT) AS Expr1
    FROM [User]
    WHERE (UserID = 20070022)

And it only returns `TRUE` if the `UserID` exists on the table. I want it to return `FALSE` if the `UserID` doesn&#39;t exist on the table.

Return Boolean Value on SQL Select Statement

I want to close the existing connections to an MS SQL Server so that I can do a restore on that database programatically.

How to close existing connections to a DB

Is there a way to use the `Now()` function in SQL to select values with today&#39;s date? 

I was under the impression `Now()` would contain the time as well as date, but today&#39;s date would have the time set to `00:00:00` and therefore this would never match?

Comparing results with today&#39;s date?

I&#39;ve been tasked with coming up with a means of translating the following data:

    date        category        amount
    1/1/2012    ABC             1000.00
    2/1/2012    DEF             500.00
    2/1/2012    GHI             800.00
    2/10/2012   DEF             700.00
    3/1/2012    ABC             1100.00

into the following:

    date        ABC             DEF             GHI
    1/1/2012    1000.00
    2/1/2012                    500.00
    2/1/2012                                    800.00
    2/10/2012                   700.00
    3/1/2012    1100.00

The blank spots can be NULLs or blanks, either is fine, and the categories would need to be dynamic. Another possible caveat to this is that we&#39;ll be running the query in a limited capacity, which means temp tables are out. I&#39;ve tried to research and have landed on `PIVOT` but as I&#39;ve never used that before I really don&#39;t understand it, despite my best efforts to figure it out. Can anyone point me in the right direction?

SQL Server dynamic PIVOT query?

How do you rewrite expressions containing the standard `IS DISTINCT FROM` and `IS NOT DISTINCT FROM` operators in the SQL implementation in Microsoft SQL Server 2008R2 that does not support them?

How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?

If I am using the following SQL command in SQL Server 2008 to update a table with a foreign key constraint:

    ALTER TABLE Employees
    ADD FOREIGN KEY (UserID)
    REFERENCES ActiveDirectories(id)

**`UserID`** being my FK column in the `Employees` table. I&#39;m trying to reference the **`UserID`** in my `ActiveDirectories` table. I receive this error:

&gt; Foreign key &#39;UserID&#39; references invalid column &#39;UserID&#39; in referencing
&gt; table &#39;Employees&#39;.

SQL Add foreign key to existing column

I am very new to SQL.

I have a table like this:

    ID | TeamID | UserID | ElementID | PhaseID | Effort
    -----------------------------------------------------
    1  |   1    |  1      |   3       |  5     |   6.74
    2  |   1    |  1      |   3       |  6     |   8.25
    3  |   1    |  1      |   4       |  1     |   2.23
    4  |   1    |  1      |   4       |  5     |   6.8
    5  |   1    |  1      |   4       |  6     |   1.5

And I was told to get data like this

    ElementID | PhaseID1 | PhaseID5 | PhaseID6
    --------------------------------------------
        3     |   NULL   |   6.74   |   8.25
    	4     |   2.23   |   6.8    |   1.5

 
I understand I need to use PIVOT function. But can&#39;t understand it clearly. 
It would be great help if somebody can explain it in above case.(or any alternatives if any)

Understanding PIVOT function in T-SQL

I was looking for a `CONCAT` function in SQL Server 2008 R2. I [found the link  for this function][1]. But when I use this function, it gives the following error:

&gt; Msg 195, Level 15, State 10, Line 7  
&gt; &#39;CONCAT&#39; is not a recognized built-in function name.

Does the `CONCAT` function exists in SQL Server 2008 R2?

If not, how do I concatenate strings in SQL Server 2008 R2?


  [1]: http://msdn.microsoft.com/en-us/library/hh231515.aspx

How do I use the CONCAT function in SQL Server 2008 R2?

I have a table like this

    Value   String
    -------------------
    1       Cleo, Smith

I want to separate the comma delimited string into two columns

    Value  Name Surname
    -------------------
    1      Cleo   Smith

I need only two fixed extra columns 


How to split a comma-separated value to columns

I am using SQL Server Management Studio. 

I wish to save the results of a query to an excel file.  

I choose &quot;save as&quot; and then save to CSV file which I can open in excel. All good except I am missing column headers, any ideas how I get them exported?

Saving results with headers in SQL Server Management Studio

I want to determine if a value is integer (like `TryParse` in .NET). Unfortunatelly `ISNUMERIC` does not fit me because I want to parse only integers and not every kind of number. Is there such thing as `ISINT` or something?

Here is some code to make things clear. If `MY_FIELD` is not int, this code would fail:

    SELECT @MY_VAR = CAST(MY_FIELD AS INT)
    FROM MY_TABLE
    WHERE MY_OTHER_FIELD = &#39;MY_FILTER&#39;

Thank you

T-sql - determine if value is integer

I create a query with some results reused. I search a way to put the result into a variable and use it.

A simple way to see what I want something looking like this - I want this:

    DECLARE @result1 ?????

    SET @result1 = SELECT a,b,c FROM table1
    SELECT a AS val FROM @result1
    UNION
    SELECT b AS val FROM @result1
    UNION
    SELECT c AS val FROM @result1

Not this :
   
     SELECT a AS val FROM (SELECT a,b,c FROM table1)
     UNION
     SELECT b AS val FROM (SELECT a,b,c FROM table1)
     UNION
     SELECT c AS val FROM (SELECT a,b,c FROM table1)

It&#39;s not the result of this query that I&#39;m concerned with, but instead:

1. to stop selecting the result so many times - in my sample, I reselected the table 3 times

2. the query of `@result1` is usually so much more complex. So, with a variable, the code will be cleaner.

Maybe I want to much - or there&#39;s a type of local variable. Or using the type table and set data inside.

What do you suggest me?

Thank you

SQL query, store result of SELECT in local variable

I want to bulk upload csv file data to sql server 2005 from c# code but I am encountering the below error - 

&gt; Received an invalid column length from the bcp client for colid 6.

when bulk copy write to database server

Received an invalid column length from the bcp client for colid 6

I want to export my big SSMS (SQL Server Management Studio) query result (2.5m lines, 9 fields) as .csv or comma-delimited .txt (with headings). (MS SQL Server 2005 Management Studio.)

So that I can then either read it line-by-line into VBA program (to do certain calculations on the data) or do queries on it in Excel (e.g. with Microsoft Query). The calculations are complicated and I prefer to do it somewhere else than SSMS. 

If I choose ‘query result to text’ in SSMS and a small answer (few lines e.g. up to 200k) I could of course simply copy and paste to a text editor. For my large answer here I could of course copy and paste 200k or so lines at a time, 10 times, into a text editor like Ultra-Edit. (When I try all 2.5m at once, I get a memory warning inside SSMS.) But for the future I’d like a more elegant solution.

For ‘query result to file’, SSMS writes to an .rpt file always.  (When you right-click in the results window and choose ‘save as’, it gives a memory error just like above.) 

--&gt; So it looks like my only option is to have SSMS output its result to a file i.e. .rpt and then afterwards, convert the .rpt to .txt.

I assume this .rpt is a Crystal Reports file? Or isn&#39;t it. I don’t have Crystal Reports on my PC, so I cannot use that to convert the file.

When opening the .rpt in Ultra-Edit it looks fine. However in Microsoft Query in Excel, the headings doesn’t want to show.

When I simply read &amp; write the .rpt using VBA, the file halves in size. (330meg to 180meg). In Microsoft Query the headings do show now (though the first field name has a funny leading character, which has happened to me before in other totally different situations). I do seem to be able to do meaningful pivot tables on it in Excel.

However when I open this new file in Ultra-Edit, it shows Chinese characters! Could there still be some funny characters in it somewhere?

--&gt; Is there perhaps a free (and simple/ safe) converter app available somewhere. Or should I just trust that this .txt is fine for reading into my VBA program.

Thanks

Convert SSMS .rpt output file to .txt/.csv

If I have an insert statement such as:

    INSERT INTO MyTable
    (  
      Name,
      Address,
      PhoneNo
    )
    VALUES
    (
      &#39;Yatrix&#39;,
       &#39;1234 Address Stuff&#39;,
       &#39;1112223333&#39;
    )

How do I set `@var INT` to the new row&#39;s identity value (called `Id`) using the OUTPUT clause? I&#39;ve seen samples of putting INSERTED.Name into table variables, for example, but I can&#39;t get it into a non-table variable. 

I&#39;ve tried `OUPUT INSERTED.Id AS @var`, `SET @var = INSERTED.Id`, but neither have worked.

How do I use an INSERT statement&#39;s OUTPUT clause to get the identity value?

I have two SQL Servers (both 2005 version).

I want to migrate several tables from one to another.

I have tried:

* On source server I have right clicked on the database, selected `Tasks/Generate scripts`.
The problem is that under `Table/View options` there is no `Script data` option.

* Then I used `Script Table As/Create script` to generate SQL files in order to create the tables on my destination server. But I still need all the data.

Then I tried using:

    SELECT * 
    INTO [destination server].[destination database].[dbo].[destination table] 
    FROM [source server].[source database].[dbo].[source table]

But I get the error:

&gt; Object contains more than the maximum number of prefixes. Maximum is
&gt; 2.

Can someone please point me to the right solution to my problem?

Content Type	Original Author	Original Content on Stackoverflow
Question	kingrichard2005	View Question on Stackoverflow
Solution 1 - Sql	David Brabant	View Answer on Stackoverflow

SQL Server Invalid Column name after adding new column

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

What is the purpose of the Java Constant Pool?

Fuzzy String Comparison

Attributions