[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Remove all spaces from an sql query

Posted on 2004-08-02
3
Medium Priority
?
1,769 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
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

834 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