SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 replace blank spaces
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  09:06:53  Show Profile  Visit funketekun's Homepage  Reply with Quote
i have a field with blank spaces.
i wanna replace the spaces with just one spaces. ihave 500 fields in 500 tables.

any input will be appreacited.

i have something like this but its not working.

declare @field varchar(50)
declare @minVoter int
declare @maxVoter int
declare @tableName varchar(20)

set @tablename = '00001'


-- select ad_str1 from [00170]

select @minVoter = min(id_voter),
@maxVoter = max(id_voter) from quotename(@tablename)


while (@minVoter <= @maxVoter)
begin
select @field = ad_str1
from quotename(@tablename)
where id_voter = @MinVoter


update [00170]
set ad_str1 = replace(@field, ' ', ' ')

select @minVoter = min(id_voter)
from quotename(@tablename)
where id_voter > @minvoter

end

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/03/2006 :  09:08:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
THIS IS DYNAMIC SQL AGAIN!
Did you learn nothing from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72882 ?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  09:10:19  Show Profile  Visit funketekun's Homepage  Reply with Quote
peso, is there anyway to do it without using dynamic sql?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/03/2006 :  09:14:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. If you create the "superview" as described in the post linked above.
declare @field varchar(50)
declare @minVoter int
declare @maxVoter int
declare @tableName varchar(20)

set @tablename = '00001'

select	@minVoter = min(id_voter), 
	@maxVoter = max(id_voter)
from	vwMyHugeView


while (@minVoter <= @maxVoter)
	begin
		select	@field = ad_str1
		from	myhugeview
		where	id_voter = @MinVoter
			and tablename = @tablename

		update [00170]
		set ad_str1 = replace(@field, '  ', ' ')-- this will update ALL rows in [00170].
		                                        -- Where is the WHERE?

		select	@minVoter = min(id_voter)
		from	vwMyHugeView
		where	id_voter > @minVoter
	end

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/03/2006 09:17:22
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/03/2006 :  09:56:45  Show Profile  Reply with Quote
See also:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Replace+multiple+spaces,with+single+space

Kristen
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  10:23:45  Show Profile  Visit funketekun's Homepage  Reply with Quote
kristen, i tried that link. But people are just making fun of others. So they are not seriously helping.
thats why i opened a new thread.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/03/2006 :  10:35:37  Show Profile  Reply with Quote
"i tried that link."

So what's wrong / your difficulty with that approach then?

Kristen
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

USA
292 Posts

Posted - 10/03/2006 :  10:37:57  Show Profile  Reply with Quote
I'm confused are you trying to update one field in 500 tables or 500 fields in 500 tables?
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  10:44:54  Show Profile  Visit funketekun's Homepage  Reply with Quote
1 field in 1 tables. I have 500 tables. That makes a total of 500 fields.
The fields have 2,3, even 4 blank spaces. I want to replace them with 1 blank space.

fields
-------------------

my Names is---joeNak
my name is--joeNak
my name is----joe nak


result-field
---------------------
my name is joeNak
my name is joenak
my name is joenak



the ---- are spaces.

Edited by - funketekun on 10/03/2006 10:45:25
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

USA
292 Posts

Posted - 10/03/2006 :  11:23:16  Show Profile  Reply with Quote
Ok.

1. I'd take Peso's suggestion and create a super-view to update.

2. Create a function to do cleaning:

Create Function [dbo].[fn_CleanSpaces]
(@StrToClean varchar(8000))
Returns varchar(8000)
As

Begin
Declare @Clean varchar(8000)

Set @Clean = LTrim(RTrim(@StrToClean))

While CharIndex(' ', @Clean, 0) <> 0
Set @Clean = Replace(@Clean, ' ', ' ')

Return @Clean
End

3. Update the super-view.

--typo

Edited by - JoeNak on 10/03/2006 11:24:04
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/03/2006 :  13:54:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't get it.
You have 500 tables, with only 1 column in each of them?

Here is a really valuable tip for you!

1) Create a new table
2) Add two columns, TableName and Fields.
3) Move all previous data from the 500 fields to the new table, with the name of the table as column data

Now the data is somewhat normalized and much, much easier to maintain!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2165 Posts

Posted - 10/03/2006 :  14:34:57  Show Profile  Reply with Quote
Peter: Heaven forbid! That is way, way, WAY too easy. NO challenge. What are you thinking?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

funketekun
Constraint Violating Yak Guru

Australia
491 Posts

Posted - 10/03/2006 :  14:50:25  Show Profile  Visit funketekun's Homepage  Reply with Quote
peso, the tables have around 20 fields each. I only need to update one field. Replace the multiple spaces, remember.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 10/04/2006 :  00:44:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Funketekun: Then create a new table with 20+1 columns!

DonAtWork: I was stunned by the above stated fact...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000