Tuesday, February 1, 2011

Lines of a file to list in SQL query


This is a simple Perl script to convert lines in a text file to list that can be used in WHERE clause of SQL. For example, if file that contains lines like this

aaa
bbb

 ccc
ddd eee

fff

The script converts output to
('aaa', 'bbb', 'ccc', 'ddd eee', 'fff')


#!/usr/bin/perl

# Usage:
#   perl sql_list.pl < filename

$first_string = 1;

print "(";

while (<STDIN>)
{
 s/^\s+//;     # Remove leading spaces
 s/\s+$//;     # Remove trailing spaces
 next if /^$/; # Skip blank lines

 # Print first string as {'first'}
 #         and others as {, 'others'}
 print $first_string ? "'$_'" : ", '$_'" ;

 $first_string = 0; # No more first string
}

print ")\n";