CODEWITHSUNDEEP
sql
mithra
mithra

Reputation:

Query to replace a comma in SQL?

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.

Upvotes: 7

Views: 16686

Answers (4)

Lokesh
Lokesh

Reputation: 11

select replace(replace(replace(replace('LOKO, , , kkslksl  ',' ',''),',','<>'),'><',''),'<>',',') from dual

Upvotes: 1

Bigballs
Bigballs

Reputation: 3809

Use replace when you concat your string for insert :

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

Upvotes: 0

splattne
splattne

Reputation: 104040

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.

Upvotes: 15

Irmantas
Irmantas

Reputation: 3321

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

Upvotes: 1

Related Questions