string clipping in dynamic sql

    UPDATE RecHead SET ShortRecipeName = RecipeName

I am calling a MSSQL driver dynamically with this sql statement. I am getting error 'string or binary data would be truncated'
I assume I must clip the RecipeName string to overcome this error but can't seem to find function that accomplishes this.

Any suggestions appreciated.
JoeSnyderJrAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
UPDATE RecHead
SET ShortRecipeName = LEFT(RecipeName, LEN(ShortRecipeName))
0
 
chapmandewCommented:
use the LEFT function to cut the last digits off

  UPDATE RecHead SET ShortRecipeName = LEFT(RecipeName, 30)
0
 
chapmandewCommented:
in the example I gave, it takes the first 30 characters.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dreadyCommented:
If you have a RecipeName and ShortRecipename, it seems strange to me to put the long version in the shortName field;
BUt if you just want to cut it off, you could do something like:

UPDATE RecHead SET ShortRecipeName = Substring(RecipeName, 0, LEN)

where you should replace LEN with the length of the shortRecipeName field.

~dready
0
 
Scott PletcherSenior DBACommented:
D'OH, I meant to lookup the length of the actual column and put that in there.


DECLARE @ShortLength INT
SELECT @ShortLength = CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'dbo'
    AND table_name = 'tablename'

UPDATE RecHead
SET ShortRecipeName = LEFT(RecipeName, @ShortLength)
0
 
Scott PletcherSenior DBACommented:
DOUBLE D'OH:
I knew there was a function for that but forgot it temporarily ... then I remembered :-) :


UPDATE RecHead
SET ShortRecipeName = LEFT(RecipeName, COL_LENGTH ('RecHead' , 'ShortRecipeName'))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.