Updating 1k rows by removing extra spaces in them,

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

General SQL Server Forums

New to SQL Server Programming

Updating 1k rows by removing extra spaces in them,

Author

Topic

theitvideos
Starting Member

6 Posts

Posted - 2011-02-21 : 04:48:14

Hi there,

This seems to be a great place to be. Ok I'll quickly get to the question :) I am working on a SQL Server 2008 Database table that has over 1000 rows.

I need to update rows by removing extra spaces between words. For example:-

MyColumnName
This   is simple    Data in   a row
This is    the    second row

As you can see, this above 2 rows have extra spaces in them. How do I update this column to have only a single space between words.

Kindly reply.

All comments and feedback are welcomed :)

Thank you!

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2011-02-21 : 05:03:00

simple way is to replace( , )

means replace double space with single space

you may have to repeat your update query

ofcourase there are sophisticated solutions for this

theitvideos
Starting Member

6 Posts

Posted - 2011-02-21 : 05:18:37

quote:
Originally posted by krishna_yess

simple way is to replace( , )

means replace double space with single space

you may have to repeat your update query

ofcourase there are sophisticated solutions for this

Thank for the reply. Can you suggest any solution other than replace as I have more than 1000 records in that column.

If we use replace( , ) for example:

UPDATE Customers SET Title = Replace(ColName, 'MISTER', 'Mr')

This will replace the values of the entire column and IF i use the where clause in the update statement, then there are over 5000 rows, I might have to manually go thru each record to choose the where condition, not very ideal.

Kindly suggest any good method that you have in mind.

Please reply thank you! :)

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2011-02-21 : 05:22:47

you said in your post you have to remove only spaces.
to do that you can use

update Customers set MyColumnName=replace( MyColumnName, ' ',' ')

this will work

replace wont replace entire column isn't it?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 05:29:45

@OP, Can you let me know if the spaces are of [Fixed Length] between words?

theitvideos
Starting Member

6 Posts

Posted - 2011-02-21 : 05:34:39

quote:
Originally posted by krishna_yess

you said in your post you have to remove only spaces.
to do that you can use

update Customers set MyColumnName=replace( MyColumnName, ' ',' ')

this will work

replace wont replace entire column isn't it?

Wow this seems to be a very easy solution. I tried it works. Now I few rows that have 3 spaces between them and some 5 spaces between them.

So I guess I might have to put 3 spaces in the replace function to replace the ones with 3 spaces and then, just put 5 spaces to remove the words that have 5 spaces in them.

Am i on the right track? :D

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 05:41:12

lol, yup now you are on th right track .. you can eliminate them this way

but it depends upon the variation in spaces

theitvideos
Starting Member

6 Posts

Posted - 2011-02-21 : 06:34:55

quote:
Originally posted by MIK_2008

lol, yup now you are on th right track .. you can eliminate them this way but it depends upon the variation in spaces

My gosh! it worked! I am so happy!

Now just one last thing.

I in the same column i have rows as:

87 02 A 52
87 02 A 53
87 02 A 54
87 02 A 55
...

I want update them to:

87 02 52 A
87 02 53 A
87 02 54 A
87 02 55 A

As you noticed in the first set, the first 3 places are identical i.e 87 02 A

and to update them we can use them in the where clause perhaps in replace function or what do you recommend? to bring the Alphabet to the last position.

kindly reply.

Thank you!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 06:47:42

Following is one way you can do it ..

Select only character portion from the column, and save it in temporary variable/column etc. Then use the replace function in order to replace All Character alphabets with '' empty string. and Append/concatenate the temp stored character(s) with your Replace function

e.g. Replace (.....) + [The temp/logic which has the character(s) info]

Cheers
MIK

P.S: if its only A as a character in the column name then you can use some thing like this; Update ColumnName=Replace(ColName,'A','')+' '+ A
else you will need to filter it as required..

theitvideos
Starting Member

6 Posts

Posted - 2011-02-21 : 07:15:44

quote:
Originally posted by MIK_2008

Following is one way you can do it ..

Select only character portion from the column, and save it in temporary variable/column etc. Then use the replace function in order to replace All Character alphabets with '' empty string. and Append/concatenate the temp stored character(s) with your Replace function

e.g. Replace (.....) + [The temp/logic which has the character(s) info]

Cheers
MIK

P.S: if its only A as a character in the column name then you can use some thing like this; Update ColumnName=Replace(ColName,'A','')+' '+ A
else you will need to filter it as required..

thanks for your reply. :) I am kinda new to this. Can you please specify the temporary table statement that will accomplish this. Alphabet position change.

I really appreciate. please reply :)

Thank you.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-21 : 07:34:56

Can you give me exact number of variation of the character alphabets in that column .. e.g. is it just one character which needs to be shifted from middle to the end of the column? or there are two, three, ... ? also the alphabets are always on the same position in your column e.g.

87 02 A 52
87 02 A 53

Not

87 A 02 52
87 02 A 53
A 87 02 54

theitvideos
Starting Member

6 Posts

Posted - 2011-02-21 : 09:17:01

quote:
Originally posted by MIK_2008

Can you give me exact number of variation of the character alphabets in that column .. e.g. is it just one character which needs to be shifted from middle to the end of the column? or there are two, three, ... ? also the alphabets are always on the same position in your column e.g.

87 02 A 52
87 02 A 53

Not

87 A 02 52
87 02 A 53
A 87 02 54

Thank you for your reply. The alphabets are only on the 3rd place (not 2nd or 1st) I only need to shift from 3rd to 4th place.

Alphabets are always on the 3rd place:

87 02 A 52
87 02 A 53

which needs to be updated to:

87 02 52 A
87 02 53 A

Kindly reply :)

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-21 : 09:34:13

it is always on the 7th chars ?


select	col, new_col = left(stuff(col, 6, 0, substring(col, 8, 3)), 10)
from	tbl

or the length of the digits may varies ?

KH
[spoiler]Time is always against us[/spoiler]

jonno99
Starting Member

1 Post

Posted - 2012-01-30 : 16:35:44

This should handle any variation of whitespace:
declare @text varchar(500)
set @text = 'delete white space' -- add as many spaces between the words as you want...
set @text = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@text,' ',' '),' ',' '),' ',' '),' ',' '),' ',' ')
print @text

Note: if you know that your whitespace count will never exceed 30 spaces, you can omit the '5 spaces' iteration.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources