?
Solved

Remove all spaces from an sql query

Posted on 2004-08-02
3
Medium Priority
?
1,768 Views
Last Modified: 2012-05-05
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
Comment
Question by:HowardNZ2003
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 11692298
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
 
LVL 5

Accepted Solution

by:
hkamal earned 1000 total points
ID: 11692869
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
 

Author Comment

by:HowardNZ2003
ID: 11753741
Thanks for the answer
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question