Insert a space in some rows and not others

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

Insert a space in some rows and not others

Author

Topic

spowell
Starting Member

23 Posts

Posted - 2009-07-16 : 13:24:21

Hi

I have a column in a table that needs updating. It contains postcodes where some have the spaces in and some do not

eg TR11PE or TR1 1PE
SW114RT or SW11 4RT

What i need to do is update all of the cells so that they all have the space in them. Because some postcodes are longer than others the space needs to be the before the last 3 characters.

The table is called contactinfo and column is postcode

Any help will be greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 13:28:06

[code]
UPDATE Table
SET PostCode=STUFF(PostCode,LEN(PostCode)-3,0,' ')
WHERE CHARINDEX(' ',PostCode)=0
[/code]

spowell
Starting Member

23 Posts

Posted - 2009-07-16 : 14:03:30

Thanks for the reply.

When I tried that though this error message came up

Server: Msg 8152, Level 16, State 6, Line 1
String or binary data would be truncated.
The statement has been terminated.

Any ideas?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-16 : 14:07:23

After you have added the space, the total length of the column has exceeded the allowable limit.

You need to find which data has the problem and correct it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 14:11:01

quote:
Originally posted by spowell

Thanks for the reply.

When I tried that though this error message came up

Server: Msg 8152, Level 16, State 6, Line 1
String or binary data would be truncated.
The statement has been terminated.

Any ideas?

seems like your column with current data does not have enough space to hold the extra space character. if you can increase the size of column

spowell
Starting Member

23 Posts

Posted - 2009-07-16 : 18:58:48

I changed the character length of the column which allowed the query to run ok but looking through the table it seems like the space has been put in the wrong place

The postcode now looks like TW 76NE when it should be TW7 6NE

How can i rectify this problem? The only solution I can think off is to remove all the spaces from all the records and try again. How would I do this though?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:44:47

[code]
--removing space
UPDATE Table
SET PostCode=REPLACE(PostCode,' ','')

--checking the correct format
SELECT PostCode AS Current,STUFF(PostCode,LEN(PostCode)-2,0,' ') AS New
FROM Table

--inserting correct space
UPDATE Table
SET PostCode=STUFF(PostCode,LEN(PostCode)-2,0,' ')
[/code]

spowell
Starting Member

23 Posts

Posted - 2009-07-17 : 17:35:41

Thanks a lot for all of your help with this one. Worked perfectly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 01:25:42

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources