How to replace column data? - 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 2005 Forums

Transact-SQL (2005)

How to replace column data?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-18 : 09:52:56

I have a column:

920303
1050620
920101
1070324

I want to replace all rows starting with '10' so they are now '200' to give me:

920303
20050620
920101
20070324

sshelper
Posting Yak Master

216 Posts

Posted - 2008-01-18 : 10:09:11

Try this:

UPDATE YourTable
SET YourColumn = '200' + SUBSTRING(YourColumn, 3, LEN(YourColumn))
WHERE LEFT(YourColumn, 2) = '10'

SQL Server Helper
http://www.sql-server-helper.com

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-01-18 : 10:41:49

Many thanks sshelper!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-21 : 10:49:55

or

UPDATE YourTable
SET YourColumn = '200' + SUBSTRING(YourColumn, 3, LEN(YourColumn))
WHERE YourColumn like '10%'

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 09:02:10

[code]-- T-SQL update syntax
UPDATE dbo.format_bulletin_information
SET YourColumn = CASE
WHEN MyDateField = '0' THEN NULL
WHEN MyDateField LIKE '10%' THEN '20' + SUBSTRING(MyDateField, 2, LEN(MyDateField))
ELSE '19' + MyDateField
END

-- Dynamic SQL
DECLARE @MyDateField VARCHAR(100),
@SQL VARCHAR(8000)

SET @MyDateField = 'DT_START'

SET @SQL = '
UPDATE dbo.format_bulletin_information
SET ' + QUOTENAME(@MyDateField) + ' = CASE
WHEN ' + QUOTENAME(@MyDateField) + ' = '0' THEN NULL
WHEN ' + QUOTENAME(@MyDateField) + ' LIKE '10%' THEN '20' + SUBSTRING(' + QUOTENAME(@MyDateField) + ', 2, LEN(' + QUOTENAME(@MyDateField) + '))
ELSE '19' + ' + QUOTENAME(@MyDateField) + '
END
'

EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 09:26:47

quote:
Originally posted by OldMySQLUser

I have a column:

920303
1050620
920101
1070324

I want to replace all rows starting with '10' so they are now '200' to give me:

920303
20050620
920101
20070324

Are you storing dates in this format?

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-04 : 10:10:35

No, I'm not storing dates in this format. Rather trying to reformat a .csv file which has been given to me to convert. After manipulating the initial string I convert the column to datetime and SQL Server 2005 does the rest.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-04 : 16:30:40

quote:

Originally posted by Peso


-- Dynamic SQL
DECLARE	@MyDateField VARCHAR(100),
	@SQL VARCHAR(8000)

SET	@MyDateField = 'DT_START'

SET	@SQL = '
UPDATE	dbo.format_bulletin_information
SET	' + QUOTENAME(@MyDateField) + ' =	CASE
				WHEN ' + QUOTENAME(@MyDateField) + ' = '0' THEN NULL
				WHEN ' + QUOTENAME(@MyDateField) + ' LIKE '10%' THEN '20' + SUBSTRING(' + QUOTENAME(@MyDateField) + ', 2, LEN(' + QUOTENAME(@MyDateField) + ')) 
				ELSE '19' + ' + QUOTENAME(@MyDateField) + '
			END
'

EXEC (@SQL)

The dynamic SQL example gave me a 'incorrect syntax near '0' error. I think this to do with the quotes, but the number of quotes looks OK.
Should the quotes around the zero be escaped in some way?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 07:48:55

what does print @SQL result?

Madhivanan

Failing to plan is Planning to fail

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2008-02-05 : 07:58:21

quote:
Originally posted by madhivanan

what does print @SQL result?

Madhivanan

Failing to plan is Planning to fail

I can't get that far. The error halts everything before I can reach the PRINT section.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:52:42

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by madhivanan

what does print @SQL result?

Madhivanan

Failing to plan is Planning to fail

I can't get that far. The error halts everything before I can reach the PRINT section.

Try

DECLARE	@MyDateField VARCHAR(100),
	@SQL VARCHAR(8000)

SET	@MyDateField = 'DT_START'

SET	@SQL = '
UPDATE	dbo.format_bulletin_information
SET	' + QUOTENAME(@MyDateField) + ' =	CASE
				WHEN ' + QUOTENAME(@MyDateField) + ' = ''0'' THEN NULL
				WHEN ' + QUOTENAME(@MyDateField) + ' LIKE ''10%'' THEN ''20'' + SUBSTRING(' + QUOTENAME(@MyDateField) + ', 2, LEN(' + QUOTENAME(@MyDateField) + ')) 
				ELSE ''19'' + ' + QUOTENAME(@MyDateField) + '
			END
'

EXEC (@SQL)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources