SQL Syntax Help

[kim]

I have a field in a MS SQL 2000 database table with data type of 'NAME (varchar)', with the length of 64.

I want to delete all values in this field that are 25 characters or longer.

What would my syntax be ?

Thank you !

[nosepicker]

DELETE YourTable
WHERE LEN(name) >= 25

[kim]

thanks NP...but it looks as if the criteria has changed.

If the field is longer than 25 characters, then delete all the remaining characters, but keep the first 25 characters.

Some data in this field has less then 25 characters, which is fine and should be left alone.

So how do I write the query that only truncates after the 25th character ?...use rtrim ?

thanks

[skhanal]

then use update statement

UPDATE YourTable
set name = substring(name,1,25)
WHERE DATALENGTH(name) >= 25

[kim]

Excellent, thank you for your time, I really appreciate it

[nosepicker]

Be careful about using DATALENGTH. First of all, it will count trailing spaces, whereas LEN will not. I don't think this will be a problem with your issue. DATALENGTH will also return the number of bytes that a particular string uses, not necessarily the number of characters. Therefore, if your data is unicode (like nchar, nvarchar, ntext), DATALENGTH will return the number of bytes, which will be twice the number of characters. For example, 'abc' in nvarchar datatype will have a LEN of 3, but a DATALENGTH of 6. If you keep your data at char or varchar, then you won't run into this difference.