4

I have columns that contain empty spaces with the data:
example:| fish | how can I update the column so my result will be : |Fish| ?
in oracle I can trim the column:

update Example set column1 = trim(column1)

I google it and i notice that ASE doesnt supoort trim.

Robert
  • 25,425
  • 8
  • 67
  • 81
Moudiz
  • 7,211
  • 22
  • 78
  • 156

2 Answers2

11

I found that str_replace(column1, ' ', '') does not actually replace the spaces.

Switching the '' for null works:

create table Example (column1 varchar(15))
insert into Example (column1) values ('| fish |')
select * from Example
-- produces "| fish |"

update Example set column1 = str_replace(column1, ' ', null)
select * from Example
-- produces "|fish|"

drop table Example
Steven
  • 129
  • 4
  • It is so strange that trying to remove a string from text with '' as the replacement string doesn't work. Thanks for pointing out that null will work. – RobbZ Feb 14 '18 at 09:17
7

You can use combine of rtrim and ltrim

update Example set column1 = rtrim(ltrim(column1))

or str_replace

update Example set column1 = str_replace(column1,' ','')
Robert
  • 25,425
  • 8
  • 67
  • 81
  • 2
    with str_replace(column1,' ','') don´t work for me. But, str_replace(column1, ' ', null), work fine for me. – jonatanes May 12 '17 at 15:50
  • 1
    @jonatanes Probably it depends on ASE's version. Probably you have got newer version than I had got 4 (sic!) years ago! The answer is accepted so it was good for the OP's ASE's version. Do you think downvoting is good for this situation? – Robert May 12 '17 at 19:40