clear spaces - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

clear spaces

Author

Topic

Bojch
Starting Member

10 Posts

Posted - 2004-10-22 : 06:37:54

i have a problem....
how can i get ride off spaces in names with a stored procedure...
i have table tblAuthor, which has Username(varchar(60)) and author_id(primary key, int).Username has value like this
mr.++++John+++++++++++Doe++++
(+ indicate empty space)

i want a result like this: mr.John Doe....

i'm newbie, so be gentle.....

thnx in advance....

p.s.
pointers are good enought, code will be splended :)

bojch

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 07:09:00

use replace.
select replace(replace(replace(replace(replace(ColumnName, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')

Go with the flow & have fun! Else fight the flow

morleyhill
Starting Member

19 Posts

Posted - 2004-10-22 : 07:13:16

Hi Bojch

Is this any good to you-

set @Value = ltrim(rtrim(@Value))
while patindex('%++%', @Value) <> 0
set @Value = replace(@Value, '++', '+')

Basically it trims all spaces from the start and end, then keeps looping round looking for any instance of two spaces and replaces them with one space until there aren't any instances of two spaces left.

Try this in Query Analyzer-
declare @Value varchar(50)
set @Value = 'Mr.++++++++++John++++++++++Doe+++++++++'

set @Value = ltrim(rtrim(@Value))
while patindex('%++%', @Value) <> 0
set @Value = replace(@Value, '++', '+')
print @Value

Obviously the ltrim/rtrim won't work unless you replace the '+' with spaces!

You could use this to write a function that you could use in your Select statement-

CREATE FUNCTION [dbo].[TrimSpace]
(
@Value varchar(60)
)
RETURNS varchar(60) AS
BEGIN
set @Value = ltrim(rtrim(@Value))
while patindex('% %', @Value) <> 0
set @Value = replace(@Value, ' ', ' ')

return @Value
END

Then you would use-
SELECT dbo.TrimSpace(UserName) as UserName

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 07:25:39

morley: this is all good but isn't that a bit slower than replace?

Go with the flow & have fun! Else fight the flow

morleyhill
Starting Member

19 Posts

Posted - 2004-10-22 : 07:29:52

spirit_1

But I'm still using REPLACE to do the work.
I'm only looping so that you don't need multiple nested replaces.
The UserName column is varchar(60) so how many nested replaces do you need to cover any possible name?
As my code actually checks to see if a REPLACE is required before doing it, it could possibly be quicker.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 07:42:45

cool...

Go with the flow & have fun! Else fight the flow

morleyhill
Starting Member

19 Posts

Posted - 2004-10-22 : 07:48:58

thanks!

Bojch
Starting Member

10 Posts

Posted - 2004-10-22 : 07:49:13

thnx a lot, i used both solutions and both works cool....
i tryed to make a function like you wroted, but
the function is not working....

maybe i did something wrong....

maybe to many white spaces or something....

bojch

morleyhill
Starting Member

19 Posts

Posted - 2004-10-22 : 07:53:03

Bojch-

Yes you're right. Looks like the spaces get jiggered when the messages get posted!
Copy this and replace each '+' with a space.

CREATE FUNCTION [dbo].[TrimSpace]
(
@Value varchar(60)
)
RETURNS varchar(60) AS
BEGIN
set @Value = ltrim(rtrim(@Value))
while patindex('%++%', @Value) <> 0
set @Value = replace(@Value, '++', '+')

return @Value
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 07:56:36

hey bojch nice homepage

where are you? in ljubljana?

Go with the flow & have fun! Else fight the flow

Bojch
Starting Member

10 Posts

Posted - 2004-10-22 : 07:58:58

morleyhill, tnhx man, you make my life easier....

slovene language:
spirit1...jup, sm v ljubljani, delam....domnevam da ti tudi...
te mogoce poznam?

bojch

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 08:01:48

yeah me too... well that depends on how old are you ... but no i don't think so...
but i guess we'll be seeing each other here...

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 08:04:41

yeah me too... well that depends on how old are you ... but no i don't think so...
but i guess we'll be seeing each other here...

Go with the flow & have fun! Else fight the flow

Bojch
Starting Member

10 Posts

Posted - 2004-10-22 : 08:07:51

i'm 21, student on faculty for computer science on Trzaska.....

we will see us here....
have fun

bojch

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 08:26:06

same faculty just electrical

absolvent

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-22 : 08:42:42

hey just for giggles,
here is an example of no loop that should only ever require 3 replaces...its probably less work than a loop, but I can't prove it right now..


declare @Value varchar(50)
set @Value = replace('Mr.++++++++++John+++++++++++Doe+++++++++','+',' ')

Select @value

Select Replace(replace(replace(@value,'  ',' +'),'+ ',''),'+','')

Corey

morleyhill
Starting Member

19 Posts

Posted - 2004-10-22 : 09:06:42

Hi Seventhnight- I like that solution!

You're right- it would be less work than a loop
and it will work for any length of string which is the reason I thought I needed a loop.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-22 : 09:09:26

HeHe how did a value like that get in the database? Someone must of went bannana's typing in a user name.

Dustin Michaels

Bojch
Starting Member

10 Posts

Posted - 2004-10-22 : 09:38:38

i was linking 3 tables with names in one with use of view and result was value shown before.....

why make life easy, if life can be difficulty :)

spirit1: nice to know some student are working on good stuff :)

bojch

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-22 : 09:41:50

so any calls from microsoft yet??
after all you were 3rd on MS Imagine Cup, no?

Go with the flow & have fun! Else fight the flow

Bojch
Starting Member

10 Posts

Posted - 2004-10-22 : 10:06:02

so, you know who we are....
yes, they called....and get us a job, but now i'm programming for another big company and i have a lot of work.....with SQL....and i must say, it is fun :-)

StudentJeCar is starting, will you come?
http://www.microsoft.com/slovenija/academia/StudentJeCar.aspx

bojch

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources