How To Enclose A Listing Of Values Into Unmarried Quotes For Sql Question - Role Microsoft Excel

Many times you lot larn a listing of values which you lot desire to depository fiscal establishment jibe inwards the database to confirm if they be inwards your tables or to larn to a greater extent than information bout them. For example, you lot got a listing of 100 stocks together with you lot desire to depository fiscal establishment jibe their final twenty-four hours closing prices inwards the database. The occupation arises when values to locomote searched are Strings e.g. VARCHAR or CHAR because VARCHAR values demand to enclosed inwards the unmarried quotes inwards virtually of the database e.g. SQL Server, Oracle or MySQL. If disclose of values are less than v thence it brand feel to add together unmarried quotes to a greater extent than or less them together with separate them past times comma manually thence that you lot tin role thence on IN clause of your SQL query, but if it's to a greater extent than than v e.g. l or 100 thence manually enclosing them into unmarried quotes volition accept a lot of fourth dimension together with virtually importantly programmers are non supposed to create such featherbrained materials manually.

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

I eventually honour a simpler solution of this occupation using Microsoft Excel, a tool which many of us know but doesn't role effectively. By using Microsoft excel you lot tin only write a formula to concatenate values from ane column amongst unmarried quotes together with comma. Later you lot tin role the Excel's fill upward technique to re-create it overall jail cellphone values together with you lot are done. Here is the instance together with formula to create so:

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

Step 1:
Copy the listing of values into the start 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 (') together with  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 together with 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 Bharat 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 withdraw the comma from the final value earlier you lot re-create them into SQL editor.

This nicely solves the occupation of converting a listing of values into unmarried quote enclosed together with comma separated values which tin locomote used amongst SQL query.

I am non a Microsoft Excel goodness but I receive got seen people using Excel to solve unlike types of problems e.g. some fourth dimension you lot larn ii lists of values together with you lot receive got to honour out which values are mutual together with which values of ane 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 help you lot inwards your day-to-day task whether you lot are a developer, back upward 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, thence delight portion amongst us.

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

Share this post