Query to replace a comma in SQL?

Question

Hi,

I have a table with the columns employee, address, city, state and zipcode. I have merged address, city, state, zipcode to a single column 'address' separating each field by comma.

My issue is, if one of the fields is null, an extra comma will be inserted. For example if city is null the resulting value will be like address,,state,zipcode. I need to remove this extra comma. How to do this? Please help.

Answer 1

You could use a case when construct

   ... = case when city is null then '' else city + ',' end

If the values are already in the database you could replace it this way:

   UPDATE tableX SET address= replace(address, ',,', ',')

Execute it N times to be sure to cover even the "all fields are null" case.

Answer 2

or you can do this manually in php

<?php

$str = 'address,,state,zipcode';

$str = preg_replace('/,{2,}/i', ',', $str);
echo $str;

?>

I believe you can do this in your language too

Answer 3

Use replace when you concat your string for insert :

REPLACE('address,,state,zipcode', ',,' , ',' )

Answer 4

What about REPLACE('address,,,,,,state,,,,,,,,zipcode', ',,' , ',' )

I need 'address,state,zipcode' as answer.