Solved

Concat and insert if not blank

Posted on 2014-01-27
8
555 Views
Last Modified: 2014-01-28
I have the following which works fine

INSERT INTO product_images (product_image_name,sku)
select concat_ws(';',image1,image2,image3),item from import
ON DUPLICATE KEY UPDATE product_image_name=concat_ws(';',image1,image2,image3)

But I want to add some conditions:
If either image1,image2 or image3 is blank (will be blank and not NULL) then don't include that particular field.
If all fields are blank then skip

Is it possible? Would this make the sql very complicated? I can live with inserting 'blank' fields (as I will just do a delete on them) but would really like the empty fields to be skipped.
0
Comment
Question by:Gary
8 Comments
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39813951
yes, it is possible. Use NULLIF() function to convert empty columns to NULLs, that will in turn be skipped by concat_ws. Also, add an additional condition to your select statement to skip all NULL values:
INSERT INTO product_images (product_image_name,sku)
select concat_ws(';',NULLIF(image1,''),NULLIF(image2,''),NULLIF(image3,'')),item 
from import WHERE NULLIF(image1,'') IS NOT NULL AND 
NULLIF(image2,'') IS NOT NULL AND
NULLIF(image3,'') IS NOT NULL
ON DUPLICATE KEY UPDATE product_image_name=concat_ws(';',NULLIF(image1,''),NULLIF(image2,''),NULLIF(image3,''))

Open in new window

0
 
LVL 58

Author Comment

by:Gary
ID: 39813961
That kinda works but it isn't pulling rows where for example image1 does have a value but image2 and image3 don't.
Presumably because you are conditioning that all cols must have a value
from import WHERE NULLIF(image1,'') IS NOT NULL AND
NULLIF(image2,'') IS NOT NULL AND
NULLIF(image3,'') IS NOT NULL


...where I just want skip the fields if they are blank.
I can live with it and do a delete on the blank fields by removing the WHERE condition unless you have a better solution.
0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 500 total points
ID: 39813987
You are right. My mistake. Change the where clause to this:
where not (NULLIF(image1,'') IS NULL AND 
NULLIF(image2,'') IS NULL AND
NULLIF(image3,'') IS NULL)

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 77

Expert Comment

by:arnold
ID: 39814018
I am unclear what it is you are looking to do.
I think it will be more efficient to leave it as you have it.
image1;image2;image3;

if you make the concat conditional (other than complicating the query) you could have results
;;;
image1;;image3
versus
image1;image3
image3
image2;image3

Presumably there is a significance placed on that order when you decode/split the data from the column based on ';'
0
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39814035
I think you are almost at the point write a function to check the condition and change your query as per required. And again if you want to having blank then use default value to blank in your database.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39814846
Not sure of the overall design, but it feels to me like the product_image_name should be a separate table rather than a variable number of image names in a single column.  Just a thought... ~Ray
0
 
LVL 58

Author Comment

by:Gary
ID: 39815495
@chaau
The amendment seems to work fine, will just do a bit more testing to make sure nothing is missing.
0
 
LVL 58

Author Comment

by:Gary
ID: 39815990
Thanks, works fine.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Attach to file (img) a unique id 8 27
send post values 7 26
Oracle Query - Return results based on minimum value 8 32
How do I fix this UPDATE error? 7 24
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question