Solved

string clipping in dynamic sql

Posted on 2008-06-17
6
208 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 35 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 40 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 50 total points
ID: 21805853
UPDATE RecHead
SET ShortRecipeName = LEFT(RecipeName, LEN(ShortRecipeName))
0
 
LVL 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now