1

SQL Server 2008 - I have a table with 10 columns and many rows in which i want to delete with a condition like delete all rows which are in particular column those are less than 75 characters (about 10 words)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3731090
  • 39
  • 1
  • 5

3 Answers3

2

As @ogixologist said...

DELETE  FROM  table_name  where  len (column_name) < 75

And here i did by using CTE check it out !!!!!!!!!

;with cte 
as
(       
    SELECT  column_name, temp = LEN ( CAST ( column_name As nvarchar(4000) ) ) from table_name
)
delete from cte where temp<=75;
  • You can cast to nvarchar before finding length or else simply use Len(column_name)
  • instead of column_name replace your nvarchar column name whichever you want
  • instead of table_name replace your table name in which your data resides
CIPHER
  • 237
  • 1
  • 4
  • 19
  • @logixologist - Ya you are absolutely correct and simply done too .....regret my way of answering ......since iam learning CTE just from few days back my mind think on that way......... – CIPHER Sep 24 '14 at 12:57
  • there is a saying in programming: there are a number of ways to skin a cat.... if you achieve the correct result either way the answer is correct. I have yet to play with CTE's ;) – logixologist Sep 24 '14 at 14:05
2

The easiest solution is to use the SQL function called len used like this: len(nameOffield)

In your case simply add the function to you where clause in the delete command like this:

DELETE FROM yourTableName where len(aParticularColumn) < 75

Update to answer: if your aParticularColumn is of datatype text or ntext you can use DATALENGTH instead of len. In this case it would be 

DELETE FROM yourTableName where DATALENGTH(aParticularColumn) < 75

Microsoft Documentation to the DATALENGTH function

logixologist
  • 3,694
  • 4
  • 28
  • 46
  • Its working for regular field but NOT to for "ntext" data type field, please advise. Thanks – user3731090 Sep 24 '14 at 14:26
  • Its recommended you move away from `ntext`: See here: http://stackoverflow.com/questions/17738662/what-is-difference-between-ntext-and-varchar-in-sql-server-2008-r2 – logixologist Sep 24 '14 at 14:30
  • Cannot change from "ntext", no permissions and cannot change permissions, any idea? – user3731090 Sep 24 '14 at 16:04
  • I think this link will help you.... You should eventually look at changing `ntext` and `text` to maybe varchar(max) but here is a solution for now: http://www.mssqltips.com/sqlservertip/1188/how-to-get-length-of-text-ntext-and-image-columns-in-sql-server/ – logixologist Sep 25 '14 at 13:04
  • I have updated the answer incase anyone else ever has the same issue. – logixologist Sep 25 '14 at 13:07
0

To achieve this you can use the below approach - PLease follow the steps as mentioned

  1. Create a temp table same as your MAIN TABLE AND Insert all the records in the temp table You can use INSERT INTO SELECT Statement to achieve the first step
  2. While inserting you will mention the column names and values - use CAST(varchar(75), [columnname]) which will truncate the data beyond 75 characters.
  3. Then you can truncate your main table and insert all the records from temp table to main table.
AK47
  • 3,707
  • 3
  • 17
  • 36
Murtaza
  • 3,045
  • 2
  • 25
  • 39
  • 1
    Thanks, Since I an not a programmer, I would appreciate if you could write the whole code so I could copy and paste. Thanks again. – user3731090 Sep 24 '14 at 10:51
  • 1
    Copy and Paste Lolllzlzzzz I will give you my bank account number, please transfer the effort hours amount.. ;) – Murtaza Sep 24 '14 at 12:56