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

Many times you lot larn a listing of values which you lot desire to banking company gibe 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 too you lot desire to banking company gibe their end twenty-four hr catamenia closing prices inwards the database. The work arises when values to hold upwardly 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 break of values are less than v hence it brand feel to add together unmarried quotes unopen to them too separate them past times comma manually hence that you lot tin plough over the axe 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 receive got a lot of fourth dimension too nigh importantly programmers are non supposed to produce such light-headed materials manually.

I did it a twain of squad earlier realizing it's also painful too the province of affairs is also frequent. I root tried to role a text editor similar Wordpad too Notepad to convert a listing of values into something I tin plough over the axe role inwards the IN clause of SQL. I tried regular expression too macros but it wasn't also tardily equally many text editors don't back upwardly find/replace on a regular expression.

I eventually notice 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 plough over the axe but write a formula to concatenate values from 1 column amongst unmarried quotes too comma. Later you lot tin plough over the axe role the Excel's create amount technique to re-create it overall jail cellphone values too you lot are done. Here is the trial too 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 root 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 (') too  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 too separated past times a comma, you lot tin plough over the axe 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 recall to take the comma from the end 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 too comma separated values which tin plough over the axe hold upwardly used amongst SQL query.

I am non a Microsoft Excel skilful but I receive got seen people using Excel to solve dissimilar types of problems e.g. some fourth dimension you lot larn 2 lists of values too you lot receive got to notice out which values are mutual too which values of 1 listing doesn't be on other, for this you lot tin plough over the axe role the VLOOKUP component subdivision of Microsoft Excel.

Influenza A virus subtype H5N1 goodness noesis of Microsoft Excel volition seriously assistance 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 part amongst us.

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

Sumber https://javarevisited.blogspot.com/

Share this post