April 7, 2009 at 11:16 pm
I would like to update a char column by adding a ']' to the end of the existing data. The data is in the form ANMS1234[001 and I want it to read ANMS1234[001]. My script below returns the error message Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated. Column1=char(42) not null and the way I understand it, my update would result in data no more than 13 characters long.
updateTable
setColumn1 = (column1 + ']')
whereColumn1 = 'ANMS2000[002'
I would like to apply this to a large number of records selecting on the value in Column1.
Grateful for any assistance
Sally
April 8, 2009 at 12:13 am
Probably There are spaces after your column1
use the following statement
UPDATE TABLE
SET COLUMN1 = RTRIM(LTRIM(Column1)) + ']'
April 8, 2009 at 7:59 am
APARNA (4/8/2009)
UPDATE TABLESET COLUMN1 = RTRIM(LTRIM(Column1)) + ']'
I Wounder why we dont have TRIM function in SQL Server. 😉
April 8, 2009 at 4:35 pm
Thanks so much. Clearly I have a lot to learn! Sally
April 9, 2009 at 8:12 am
Glad to see your issue is resolved 🙂
April 9, 2009 at 8:50 am
Vijaya Kadiyala (4/8/2009)
I Wounder why we dont have TRIM function in SQL Server. 😉
??? rtrim and ltrim don't count?
-- You can't be late until you show up.
April 10, 2009 at 9:39 am
To trim the spaces on both the sides of the string we need to use LTRIM and RTRIM. Instead of we have one function from SQL Server to do both then its easy.
April 13, 2009 at 6:09 am
Why not create your own udf that contains the LTRIM(RTRIM(value))
functionality and name it something like TrimString()?
April 13, 2009 at 7:37 am
Hi, Yes we can create TRIM UDF, Thats what i am doing right now.
==> TRIM( ,'L') for doign Left-Trim
==> TRIM( ,'R') for doign Right-Trim
==> TRIM( ) for doign Trim
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply