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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help/Advise please for converting space delimited string variable to comma delimited with quote
# 1  
Old 09-05-2016
Help/Advise please for converting space delimited string variable to comma delimited with quote

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:

Code:
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:

Code:
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:

Code:
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 Smilie. 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

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

I would want it to be like

Code:
'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 Smilie

Any advise will be much appreciated. Thanks in advance.
# 2  
Old 09-05-2016
Hello newbie_01,

Following may help you in same.
Code:
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.
Code:
'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  
Old 09-05-2016
Try also
Code:
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'

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Need help on an old post - How to convert a comma delimited string to records or lines of text?

Hi, Apologies in advance to the moderator if I am posting this the wrong way. I've searched and found the solution to an old post but as it is a very old post, I don't see an option to update it with additional question. The question I have is in relation to the following post: How to... (6 Replies)
Discussion started by: newbie_01
6 Replies

2. UNIX for Dummies Questions & Answers

How to convert a comma delimited string to records or lines of text?

Hi, I am not sure if I've posted this question before. Anyway, I previously asked about converting lines of text into a comma delimited string. Now I am needing to do the other way around ... :( :o Can anyone advise how is this possible? Example as below: Converting records/lines to... (2 Replies)
Discussion started by: newbie_01
2 Replies

3. UNIX for Dummies Questions & Answers

How to change a line of text to a comma delimited string?

Hi, Is there a one-liner that I can use to change a line of text into a comma delimited string? For example, convert user1 user2 user3 user4to user1,user2,user3,user4Currently using while read x, although got the extra comma at the end that I have to remove manually. Please... (5 Replies)
Discussion started by: newbie_01
5 Replies

4. Shell Programming and Scripting

Converting varied space delimited file to Pipedemilited

Hi all, I have source file, data looks like 12345 abc def 01 / 001200 C 2000 12345 abc def 01 / 001200 C 2500 12345 abcd def 01 / 001200 C 3500 18945 xyz pqr 01 / 009900 D 4000 5000 2800 9900 Expected ouput... (3 Replies)
Discussion started by: srk409
3 Replies

5. Shell Programming and Scripting

How to make tab delimited file to space delimited?

Hi How to make tab delimited file to space delimited? in put file: ABC kgy jkh ghj ash kjl o/p file: ABC kgy jkh ghj ash kjl Use code tags, thanks. (1 Reply)
Discussion started by: jagdishrout
1 Replies

6. Shell Programming and Scripting

Need a script to convert comma delimited files to semi colon delimited

Hi All, I need a unix script to convert .csv files to .skv files (changing a comma delimited file to a semi colon delimited file). I am a unix newbie and so don't know where to start. The script will be scheduled using cron and needs to convert each .csv file in a particular folder to a .skv... (4 Replies)
Discussion started by: CarpKing
4 Replies

7. Shell Programming and Scripting

Help with converting Pipe delimited file to Tab Delimited

I have a file which was pipe delimited, I need to make it tab delimited. I tried with sed but no use cat file | sed 's/|//t/g' The above command substituted "/t" not tab in the place of pipe. Sample file: abc|123|2012-01-30|2012-04-28|xyz have to convert to: abc 123... (6 Replies)
Discussion started by: karumudi7
6 Replies

8. Shell Programming and Scripting

Converting comma separated to pipe delimited file

Hi, I came across a very good script to convert a comma seperated to pipe delimited file in this forum. the script serves most of the requirement but looks like it does not handle embedded double quotes and commas i.e if the input is like 1234, "value","first,second", "LDC5"monitor",... (15 Replies)
Discussion started by: anijan
15 Replies

9. UNIX for Dummies Questions & Answers

Converting Space delimited file to Tab delimited file

Hi all, I have a file with single white space delimited values, I want to convert them to a tab delimited file. I tried sed, tr ... but nothing is working. Thanks, Rajeevan D (16 Replies)
Discussion started by: jeevs81
16 Replies

10. Shell Programming and Scripting

Converting Tab delimited file to Comma delimited file in Unix

Hi, Can anyone let me know on how to convert a Tab delimited file to Comma delimited file in Unix Thanks!! (22 Replies)
Discussion started by: charan81
22 Replies
Login or Register to Ask a Question