how do I retrieve data from a sql table with huge number of inputs for a single column

Question

I have a Company table in SQL Server and I would like to retrieve list of data related to particular companies and list of companies is very huge of around 200 company names and I am trying to use IN clause of T-SQL which is complicating the retrieval as few the companies have special characters in their name like O'Brien and so its throwing up an error as it is obvious.

SELECT *
FROM COMPANY
WHERE COMPANYNAME  IN
    ('Archer Daniels Midland'
    'Shell Trading (US) Company - Financial'
    'Redwood Fund, LLC'
    'Bunge Global Agribusiness - Matt Thibodeaux'
    'PTG, LLC'
    'Morgan Stanley Capital Group'
    'Vitol Inc.'..
.....
....
.....)

Above is the script that is not working for obvious reasons, is there any way I can input those company names from an excel file and retrieve the data?

Is it really sql-server and mysql? what client are you using? Also http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause — Conrad Frix, Mar 17 '14 at 19:02
@Dayakar ok and where is this sql running? Are you just running it in a SSMS or do you plan to run it from another client (e.g. excel macro, .NET app, php etc) — Conrad Frix, Mar 17 '14 at 19:10
Is `COMANYNAME` the name of the table, or is it `COMPANYNAME`? — Mark C., Mar 17 '14 at 19:43
it is COMPANYNAME though, Thats a typo, nothing to do with the result — DoIt, Mar 17 '14 at 19:45

score 1 · Accepted Answer · answered Mar 17 '14 at 19:01

1

The easiest way would be to make a table and join it:

CREATE TABLE dbo.IncludedCompanies (CompanyName varchar(1000)

INSERT INTO dbo.IncludedCompanies
VALUES
('Archer Daniels Midland'),
('PTG, LLC')
...

SELECT *
FROM Company C
JOIN IncludedCompanies IC
ON C.CompanyName = IC.CompanyName

answered Mar 17 '14 at 19:01

JNK

63,321
15
122
138

Seems to be a good idea but as I already mentioned I do have a huge list of company names in an excel file with special characters in them which doesn't allow me to even insert them to the new table. – DoIt Mar 17 '14 at 19:42
2

1 - 200 names is not huge. 2 - You have two unrelated issues here (a- how to get the list into the database; b- how to filter on that list) – JNK Mar 17 '14 at 19:46

score 0 · Answer 2 · answered Mar 17 '14 at 19:08

I do not think that mysql knows how to handle excel format, but you can fix your query. Check how complicated names are stored in database (check if they have escape characters in them or anything else".

Replace all ' with \' in your query and it will take care of the ' characters

mysql> select now() as 'O\'Brian'; returns

O'Brian

2014-03-17 15:06:39

score 0 · Answer 3 · answered Mar 17 '14 at 19:14

So i'm guessing you have a excel sheet with a column containing these names, and you want to use this in your where clause. In addition, some of the values have special characters in them, which needs to be escaped.

First thing you do is to escape the '-characters. You do this in excel, with a search replace for all occurences of ' with '' (the escaped version in sqlserver (\' in MySQL.)) Then, create a new column on each side side of your companies column, and in the first row input a ' on the left hand side, and ', on the right. Then use the copy cell functionality (the little square in the bottom right of the cell when you select it) to copy the cells to the left and right to all the rows, as far as the company list goes (just grab the square and pull it downwards..)

Then, take your list, now containing three columns and x rows and paste it into your favorite text editor. It should look something like this:

'      Company#1     ',
'      Company with special '' char     ',
[...]
'      Last company     ',

Now, you will have some whitespace to get rid of. Use search replace and replace two space characters with nothing, and repeat (or take the space from the first ' to the start of the text and replace this with nothing.

Now, you should have a list of:

'Company#1',
'Company with special '' char',
[...]
'Last company',

Remove the last comma, and you'll have a valid list of parameters to your in-clause (or a (temporary) table if you want to keep your query a bit cleaner.)

how do I retrieve data from a sql table with huge number of inputs for a single column

3 Answers3