HowardNZ2003
asked on
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)
but I would like to remove all spaces, including those within the returned fields
Thanks.
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)
but I would like to remove all spaces, including those within the returned fields
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the answer
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.