How To Enclose A Listing Of Values Into Unmarried Quotes For Sql Enquiry - Purpose Microsoft Excel

Many times you lot instruct a listing of values which you lot desire to banking concern jibe inwards the database to confirm if they be inwards your tables or to instruct to a greater extent than information bout them. For example, you lot got a listing of 100 stocks in addition to you lot desire to banking concern jibe their final twenty-four hours closing prices inwards the database. The work arises when values to survive searched are Strings e.g. VARCHAR or CHAR because VARCHAR values demand to enclosed inwards the unmarried quotes inwards nigh of the database e.g. SQL Server, Oracle or MySQL. If position out of values are less than v hence it brand feel to add together unmarried quotes around them in addition to separate them past times comma manually hence that you lot tin role hence on IN clause of your SQL query, but if it's to a greater extent than than v e.g. fifty or 100 hence manually enclosing them into unmarried quotes volition accept a lot of fourth dimension in addition to nigh importantly programmers are non supposed to produce such featherbrained materials manually.

I did it a distich of squad earlier realizing it's equally good painful in addition to the province of affairs is equally good frequent. I outset tried to role a text editor similar Wordpad in addition to Notepad to convert a listing of values into something I tin role inwards the IN clause of SQL. I tried regular expression in addition to macros but it wasn't equally good slow equally many text editors don't back upwardly find/replace on a regular expression.

I eventually uncovering a simpler solution of this work using Microsoft Excel, a tool which many of us know but doesn't role effectively. By using Microsoft excel you lot tin but write a formula to concatenate values from 1 column amongst unmarried quotes in addition to comma. Later you lot tin role the Excel's fill upwardly technique to re-create it overall jail cellphone values in addition to you lot are done. Here is the trial in addition to formula to produce so:

Steps to enclose a listing of String into unmarried quotes using Microsoft Excel

Step 1:
Copy the listing of values into the outset column (A) inwards your Excel Work Sheet

Step 2:
Copy the next formula into minute column (B) on the first cell
=CHAR(39) & A1 & CHAR(39) & CHAR(44)

Here 39 is the ASCII value of unmarried quote (') in addition to  44 is the ASCII value of the comma.


Step 3:
Drag this formula to the remaining jail cellphone equally shown below:

Step 4:
Now column B has values which are enclosed inwards unmarried quotes in addition to separated past times a comma, you lot tin straight role them inwards the SQL query equally shown below:

SELECT ClosingPrice from StockDetails where Company Name IN ( 'Ambalal Sarabhai Enterprises Ltd', 'Assambrook Ltd', 'PH Capital Ltd', 'Mavi Industries Ltd', 'Oswal Spinning & Weaving Mills Ltd', 'TPI Republic of Republic of India Ltd', 'Zuari Industries Ltd', 'Oscar Investments Ltd', 'Maestros Mediline Systems Ltd', 'Mediaone Global Entertainment Ltd', 'GEE Ltd', 'Kaycee Industries Ltd', 'Rathi Steel & Power Ltd', 'Clutch Auto Ltd' )

Just recollect to take the comma from the final value earlier you lot re-create them into SQL editor.

This nicely solves the work of converting a listing of values into unmarried quote enclosed in addition to comma separated values which tin survive used amongst SQL query.

I am non a Microsoft Excel proficient but I receive got seen people using Excel to solve unlike types of problems e.g. some fourth dimension you lot instruct 2 lists of values in addition to you lot receive got to uncovering out which values are mutual in addition to which values of 1 listing doesn't be on other, for this you lot tin role the VLOOKUP business office of Microsoft Excel.

Influenza A virus subtype H5N1 goodness cognition of Microsoft Excel volition seriously tending you lot inwards your day-to-day task whether you lot are a developer, back upwardly professional, projection managers or whatever technical user. I am working on to compile some useful Excel tips for Java developers similar this one. If you lot receive got any, hence delight portion amongst us.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post