merge columns

[pearlofperls]

I have 2 columns from sql extract and for each row either column has values in each row. I need to be able to combine the columns so there are no blank values.

example

column1 column2 column3 column 4column 5
blank abcdef 123 john lola
ghijkl blank 456 don sue
mnop blank 789 cliff jane
blank qrstu 1011 joe missy
vwxyz blank 1213 mike donna


column1 column2 column3 column 4
abcdef 123 john lola
ghijkl 456 don sue
mnop 789 cliff jane
qrstu 1011 joe missy
vwxyz 1213 mike donna

 

[dkyrtata]

The best way is is to do this at the SQL level. In Oracle, you can use NVL(col1,col2)

In Perl, you can do something like:

$new_record=join(" ",split("\s+",$record));

However this will not preserve the spaces you have between fields. Is that important? I am also assuming that your reference of "blank" are actual spaces in your text. Please clarify.

If the word "blank" is physically part of the text, you may be able to use a regex to replace it with the desired field

 

[max1x]

Much easier to modify your SQL query, or if you need an output, use a delimited by | or something. It'll be easeir to split and maintain.

 

[pearlofperls]

it is sybase db and looking on web(

http://denis.sri.com/Oracle/Translator.html)

NVL equivalent is ISNULL() which i tried to no avail

my example where i wrote blank there is no value, but i belive it is not NULL as above command doesnt work

i tried
select ISNULL(col1,col2) from table

 

[dkyrtata]

In Oracle, you can handle the blank(s)

1) by using the RTRIM() or LTRIM() function to strip all blanks (SYBASE equivalent is the CONVERT() function according to the web site you provided). This will evaluate to a zero length string which equates to NULL in Oracle. So you can do NVL(LTRIM(col1,col2)). If you use this solution, verify that zero length strings equate to NULL in SYBASE

2) by using DECODE() to test for 1 blank. The DECODE() function is a primitive IF statement that can be used in a SQL command. For example, DECODE(col1,' ', col2, col1). This reads as

IF col1=' ' THEN col2 ELSE col1


If any of these suggestions do not work, try the Perl solution I provided (untested). But keep in mind all redundant spaces will become 1 space (or as many spaces specified in the join() function

If preserving the spaces is important do what Max1x suggests: Modify your SQL to insert a delimiter between fields and use the Perl split() command to split the text on that delimiter.

Let me know what you come up with

 

[pearlofperls]

LTRIM worked

select ISNULL(LTRIM(col1,col2)) from table

thanks