|
KSH SQL query to variables |
Steve Terry
Member #1,989
March 2002
|
I'm trying to do a simple SQL query in a Korn Shell script on UNIX where I need to assign the column output to three variables. The problem comes when the columns can have spaces. For example: I was going to parse my_var using cut to cut out the column data, the problem comes from the fact that the SQL command is executed on the shell and then assigned to the variable with all extra spaces removed. What I mean is that COLUMN1 COLUMN2 COLUMN3 ------- ------- ---------------- foo bar this is column 3 becomes just "foo bar this is column 3" this is pretty much impossible to parse without the column spacing. Do any of you know a way around this or a better way other than redirecting the output to a file and parsing it? ___________________________________ |
kazzmir
Member #1,786
December 2001
|
Will the first two columns contain data without spaces in them? $ echo 'foo bar this is column 3' | awk -F" " '{print $1}' foo $ echo 'foo bar this is column 3' | awk -F" " '{print $2}' bar $ echo 'foo bar this is column 3' | awk -F" " '{print $3}' this Oops that last one didn't work.. ugh time to read up on awk. |
Steve Terry
Member #1,989
March 2002
|
I tried awk already ___________________________________ |
gnolam
Member #2,030
March 2002
|
Concatenate delimiters of your own choosing to the columns in the SELECT? -- |
Crazy Photon
Member #2,588
July 2002
|
gnolam said: Concatenate delimiters of your own choosing to the columns in the SELECT? ^ This, been there already with this problem and this is how I solved it. Just be careful which delimiter you use (don't pick one that might appear in the column values), and then you can tell awk to use it as a delimiter instead of whitespace. ----- |
Steve Terry
Member #1,989
March 2002
|
How to do that? ___________________________________ |
Matthew Leverton
Supreme Loser
January 1999
|
select column1, 'X', column2 from foo where whatever |
Crazy Photon
Member #2,588
July 2002
|
You can also use CONCAT() to produce just one column with the concatenation of the columns and delimiters, in case there are extra spaces between the column and the delimiter on the result of running the query. ----- |
|