truncate - 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

General SQL Server Forums

New to SQL Server Programming

truncate

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 06:39:33

Can I truncate a column in sql server 2005

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 06:55:21

how do you mean?

Do you mean TRUNCATE as in the SQL keyword?

Or do you mean truncate to x characters in a string?

if so then yes -- look up LEFT in the help.

Post an example if that's not clear.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-14 : 06:56:58

I am afraid it is not possible. May I know the reason you want to Truncate a Column & not the Table?

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 07:10:28

I have null values in one column .But i need the column for later use so only asked about this ...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 07:34:56

so you want to do what to the column?

It's not clear what you want to do.

Do you want to NULLIFY every value in the column?

If so, that's easy. its an update statement with no where clause.

Or do you want something else.

WE NEED AN EXAMPLE if we are going to help you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 07:57:17

abc efg hij
aaa bbb ccc
aa2 bb2 cc2
null 112 null
null 123 null
null 546 null

i need to eliminate the rows contains null

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 07:58:23

If i can truncate then it is easy to insert values by using where condition

DuncanP
Starting Member

12 Posts

Posted - 2010-09-14 : 07:59:22

Do you mean truncate or delete? If delete, you can just run the delete command with WHERE <column> IS NULL.

Duncan

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 08:01:53

what are the columns called?

if it is like this:


DECLARE @foo TABLE (
	[col1] VARCHAR(255)
	, [col2] VARCHAR(255)
	, [col3] VARCHAR(255)
	)
INSERT @foo ([col1], [col2], [col3])
      SELECT 'abc', 'efg', 'hij'
UNION SELECT 'aaa', 'bbb', 'ccc'
UNION SELECT 'aa2', 'bb2', 'cc2'
UNION SELECT null, '112', null
UNION SELECT null, '123', null
UNION SELECT null, '546', null

SELECT * FROM @foo

-- Remove the rows with null

DELETE @foo
WHERE
	[col1] IS NULL
	AND [col3] IS NULL

SELECT * FROM @foo

You are making it very hard for us to help you. Just stop for a moment and think how to explain the situation to someone who doesn't know the tables involved.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 08:06:07

quote:
Originally posted by DuncanP

Do you mean truncate or delete? If delete, you can just run the delete command with WHERE <column> IS NULL.

Duncan

I know about delete .. It will delete the entire column... But i don't like that..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-14 : 08:08:16

My table contains 5086 rows ...
Is it easy ?

kashyap.2000
Starting Member

22 Posts

Posted - 2010-09-14 : 09:23:48

I guess you are trying to delete all date in that particular column rather tham deleting whole column, if this is what you want then you can execute the following command.

update [put your table name] set [put your column name]=''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-14 : 10:14:21

quote:
Originally posted by jafrywilson

I know about delete .. It will delete the entire column... But i don't like that..

No. DELETE acts horizontally on whole record over all columns.
UPDATE acts vertically on one or more columns on a restricted set of records defined by an existing WHERE clause.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-14 : 10:15:41

quote:
Originally posted by jafrywilson

abc efg hij
aaa bbb ccc
aa2 bb2 cc2
null 112 null
null 123 null
null 546 null

i need to eliminate the rows contains null

DELETE FROM Table1 WHERE Col1 IS NULL OR Col2 IS NULL OR Col3 IS NULL

N 56°04'39.26"
E 12°55'05.63"

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-15 : 00:52:05

Tnx Peso...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources