Solved

Remove all spaces from an sql query

Posted on 2004-08-02
3
1,765 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 250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Building a cohesive image for your brand is vital to making an impression on consumers. When the economy is tough, brands do better than unbranded  products. This can have a huge impact on your long-term profits, as the economy goes up and down.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 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