Help/Advise please for converting space delimited string variable to comma delimited with quote

Shell Programming and Scripting
1

Hi,

I am wanting to create a script that will construct a SQL statement based on a a space delimited string that it read from a config file.

Example of the SQL will be

For example, it will read a string like "AAA BBB CCC" and assign to a variable named IN_STRING.

I then concatenate this variable to DEFAULT_USERS so that IN_STRING="$DEFAULT_USERS $IN_STRING". DEFAULT_USERS contains "SYS SYSTEM DBSNMP RMAN"

The SQL statement will then be as below:

select username, account_status
from  dba_users
where username not in 
( $IN_STRING )

I am converting the IN_STRING variable so it will be 'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC' so that the resulting SQL will now be as below:

select username, account_status
from  dba_users
where username not in 
( 'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC' )

At the moment, I am doing as below:

IN_STRING=`echo $IN_STRING | awk '{ for(i=1; i < NF; i++) printf "\x27" $i "\x27, " ;  print "\x27"$NF"\x27" }'`

It is not the most elegant of awk but it does the trick. I am sure there will awk gurus around here who can make it look better. Feel free to recommend a more 'elegant' awk construct.

While this work, it does not look nice as the string gets longer :o. For example, the list might spill to 20+ space delimited word.

Can someone please advise if I can use awk to convert the string in such a way that the there will be 5 or 6 values per line? For example, instead of having the following in one line

'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG', 'HHH', 'III', 'JJJ', 'KKK', 'LLL'

I would want it to be like

'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 
'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 
'GGG', 'HHH', 'III', 'JJJ', 'KKK',
'LLL'

At the moment, I am thinking of looping thru the space delimited list, putting them into array and then going thru a loop and doing the construct. Kinda hoping I will be able to do the same using awk :frowning:

Any advise will be much appreciated. Thanks in advance.

2

Hello newbie_01,

Following may help you in same.

echo "SYS SYSTEM DBSNMP RMAN AAA BBB CCC DDD EEE FFF GGG HHH III JJJ KKK LLL MMM NNN OOO PPP QQQ RRR SSS TTT AAA BBB CCC"  | awk -vs1="'" '{for(i=1;i<=NF;i++){printf("%s%s%s%s",s1,$i,s1,i%5==0?RS:(i==NF?RS:OFS))}}' OFS=", "

Output will be as follows.

'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA'
'BBB', 'CCC', 'DDD', 'EEE', 'FFF'
'GGG', 'HHH', 'III', 'JJJ', 'KKK'
'LLL', 'MMM', 'NNN', 'OOO', 'PPP'
'QQQ', 'RRR', 'SSS', 'TTT', 'AAA'
'BBB', 'CCC'

So this is giving till 5 fields, similarly you could do for 6 or as per your need too by doing little fine tuning to above.

Thanks,
R. Singh

3

Try also

echo "'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG', 'HHH', 'III', 'JJJ', 'KKK', 'LLL'" | tr "," "
" | paste -sd',,,,    
'
'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA'
 'BBB', 'CCC', 'DDD', 'EEE', 'FFF'
 'GGG', 'HHH', 'III', 'JJJ', 'KKK'
 'LLL'