Solved

Remove all spaces from an sql query

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now