Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1772
  • Last Modified:

Remove all spaces from an sql query

Hi,


when running an sql query I'd like to remove all spaces from a field. I know you can run

select rtrim(name),ltrim(address) from contacts

but I would like to remove all spaces, including those within the returned fields

Thanks.
0
HowardNZ2003
Asked:
HowardNZ2003
1 Solution
 
Jan FranekCommented:
Hi

unfortunately, there's no function, that will remove all spaces. But it can be done. I see several ways:

1. if you are on ASE with Java support, you can build SQLJ function, that will remove spaces and use this function in your query

2. this will remove first space in yourcolumn: case when charindex( " ", yourcolumn ) > 0  then substring( yourcolumn, 1, charindex( " ", yourcolumn ) - 1 ) + substring( yourcolumn, charindex( " ", yourcolumn ) + 1, char_length( yourcolumn ) - charindex( " ", yourcolumn )) else yourcolumn end

You can use it this way - insert your resultset into temp table and run update query that will replace yourcolumn with above expression until there are some spaces - exists ( select 1 from #temp where charindex( " ", yourcolumn ) > 0)

3. you can build stored procedure with one parameter (input / output), that will remove all spaces from given parameter. Then use this SP this way - again store your result into temp table, that use cursor to go through all rows and apply this SP on yourcolumn.
0
 
hkamalCommented:
The code to remove spaces is as per example:

DECLARE @name VARCHAR(50), @address VARCHAR(255)
select @name="george w bush", @address="white house capitol hill washington dc usa"
while charindex(" ",@name) > 0
  select @name=substring(@name, 1, charindex(" ",@name)-1)+substring(@name, charindex(" ",@name)+1, 255)
while charindex(" ",@address) > 0
  select @address=substring(@address, 1, charindex(" ",@address)-1)+substring(@address, charindex(" ",@address)+1, 255)
select @name, @address

If you wish to store the values that way, you have to either choose which version you keep (with / without spces) or duplicate columns). You could then an update the table to process existing values then create an insert/update trigger to run the above space-removal loop on the inserted/updated string
Of course this is irreversible, so once spaces are removed, they cannot be reinserted in the same postions!
0
 
HowardNZ2003Author Commented:
Thanks for the answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now