What would be the best way to use a list in a textarea to compare to values in a column in a database table?

[davidr72]

I'm trying to move a table from a MS Access database to a datasource online so I don't have to be the only one that can perform a specific task.

Right now I regularly am having to assess change requests to determine if it impacts the servers my team supports.  Currently I copy and paste the text list into a temporary table in MS Access then hit a button to run a query comparing what was in that list to my server inventory.  Works fine but now I need to move this online so others can do this in a place where we can also keep everyone using the exact same inventory and am planning on using a ColdFusion server.

So what I believe would be easiest is to create a form that has a textarea where I can just copy and paste what is in the change request and then hit a submit button and go to the next page where it would list all the servers that matched (with all the other info I also need).

Almost always the info would hit the textarea with a separate row for each server with no other delimiters, etc.

Example info in textarea:

servername1

servername2

servername3

What is the best/easiest way in the SQL code on the following page to take the values from the textarea the way they are listed and return results of any that match the server name column from that list?  What CF functions and SQL coding are needed?

I've done something in the past where I did WHERE Server IN (#PreserveSingleQuotes(Form.ServerList)#)...

But I had to input 'servername1', 'servername2', 'servername3' in the text box and with how often we'll be copying lists as show above from a text area or from an excel column I'd really like a way to get from the example above to the results I need without having to manipulate.  Sometimes the list I'm comparing against may be 300+ servers and adding that formatting is not desirable.

Any help would be appreciated.

[davidr72]

So here is a solution I came up with

<cfoutput>

<cfset Servers="#StripCR(Form.ServerList)#">

<cfset Servers2="'#Replace(Servers, "

", " ", "All")#'">

<cfset Servers3="#ToString(Servers2)#">

<cfset Servers4="#Replace(Servers3, " ", "', '", "All")#">

</cfoutput>

Then in the cfquery SQL I used

WHERE Server IN (#PreserveSingleQuotes(Servers4)#)

Right now this is working but it seems very cumbersome.  If anyone can come up with a way to simplify this please let me know.