Improve company productivity with a Business Account.Sign Up

x
?
Solved

string clipping in dynamic sql

Posted on 2008-06-17
6
Medium Priority
?
219 Views
Last Modified: 2010-03-20
    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.
0
Comment
Question by:JoeSnyderJr
  • 3
  • 2
6 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 140 total points
ID: 21805087
use the LEFT function to cut the last digits off

  UPDATE RecHead SET ShortRecipeName = LEFT(RecipeName, 30)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21805088
in the example I gave, it takes the first 30 characters.
0
 
LVL 11

Assisted Solution

by:dready
dready earned 160 total points
ID: 21805090
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 21805853
UPDATE RecHead
SET ShortRecipeName = LEFT(RecipeName, LEN(ShortRecipeName))
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 21806068
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 21806237
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

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question