How to remove spaces after comma, in a column using SQL?

Question

I want to remove the space after comma, in a column using SQL script. I am using oracle DB.

For example,

I want a space (after sathish, kumar, raghavan) to be removed from column 'name',

I want the table to look like,

likewise all the records to be modified. I tried, this query,

update incitable SET name = LTRIM(RTRIM(name));

But it didnt make any chages,

Please help on this.

Sorry for the grammar mistakes.

Why are you storing lists of values in a single column? Assuming you're stuck with that data model, is it always exactly one space after the comma, or can there be several, or other whitespace like tabs? — Alex Poole
– Alex Poole, Commented Jun 1, 2017 at 14:12
No each comma separated value has a role to play in a single dropdown in the front end. The angular grid in my front end do not accept space or tab space. — s.sathish kumar
– s.sathish kumar, Commented Jun 2, 2017 at 6:04

Gordon Linoff · Accepted Answer · 2017-06-01 14:12:34Z

5

Use replace():

UPDATE incitable
    SET name = REPLACE(name, ', ', ',');

TRIM() removes spaces at the beginning and end of the string, but not spaces in-between. Given that TRIM() is available, there is no need to use LTRIM() and RTRIM() (unless you have a fetish for SQL Server).

Note: Storing lists of values in a single column is a really, really bad idea. Oracle offers many other solutions, such as a junction table (the SQLish way to store lists), nested tables, JSON formats, and no doubt more methods.

answered Jun 1, 2017 at 14:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

s.sathish kumar Over a year ago

But will it work with values like "sss, safa, aafa, agag, agga" which has more number of sub string.

Gordon Linoff Over a year ago

@s.sathishkumar . . . It will work on any number of commas in the string.

Gordon Linoff Over a year ago

@UdayShankar . . . That is not the question being asked here. If you have another question, ask it as a new question.

Collectives™ on Stack Overflow

How to remove spaces after comma, in a column using SQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related