Link to home
Create AccountLog in
Avatar of teaone
teaone

asked on

remove spaces

how do I remove spaces and extra commas from the product_id string? When the value for product_id is null, I get a space and an extra comma.

user_id      product_id
12345       AA, BB, CC, , EE, , ,

SELECT user_id,
AA||', '||BB||', '||CC||', '||DD||', '||EE||', '||FF||', '||GG product_id
 FROM  table
Avatar of anumoses
anumoses
Flag of United States of America image

replace(replace(col_name, CHR(13), ''), CHR(10), '')

USE THIS FOR EXTRA SPACES LIKE CARRIAGE RETURN OR LINE FEED
WITH t AS (SELECT 'a, b, c (x, y, z)' col1
             FROM DUAL)
SELECT t.col1,
       REPLACE(REGEXP_REPLACE(t.col1, '(\(.*\))|(,)', '\1\2\2'), ',,', '#') new_col
  FROM t;


Try this for comma etc
In the above query use '' instead of #
WITH t AS (SELECT 'a, b, c (x, y, z), a, (xx, yy, zz), x,' col1
             FROM dual)
SELECT t.col1
     , REGEXP_REPLACE(t.col1, '(\(.*?\))|,', '\1') new_col
  FROM t;

Try this replacing t with your table



Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Can you post your code that produces the CSV?

I would suggest fixing it there instead of after the fact.

You can likely put a simple NVL call in the code that creates the CSV.
ASKER CERTIFIED SOLUTION
Avatar of anumoses
anumoses
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>>You could also try coalesce...

How will coalesce remove 'null'/missing values in a CSV?
> How will coalesce remove 'null'/missing values in a CSV?

I thought the problem here with the NULLs.  I also wanted to see the code which generates the CSV ;-). If my solution is no way related i'll take mine back.
Avatar of teaone

ASKER

this solution from anumoses' link worked:
trim(',' from regexp_replace(val, '( *, *){2,}', ','))
thanks!