Re: Auto truncate a string to be inserted to SQL table

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 01/17/05 

Date: Mon, 17 Jan 2005 12:01:08 +0100

Again (as posted twice already here), look as SET ANSI_WARNINGS OFF. This will allow the insert,
truncating the string and not returning any errors. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"BTLye" <BTLye@discussions.microsoft.com> wrote in message 
news:AD1A6B65-9871-4D1E-9EAF-8603AF089612@microsoft.com...
> Sorry for the ambiguity...
>
> Actually what I want to do is truncate the string using any method at MSSQL
> server so that the INSERT action will not prompt me for any error.
>
> There is an application written by somebody else previously and being
> deployed to lot of client PCs. The app will insert record to SQL table at the
> end of the processing. I understand that it's faster to add the control
> (truncate the string) at the app. But making this will require all the client
> PCs to deploy the enhanced app again whereby in my case is not accepted by
> the users.
>
> So I wish to look for any alternative to do it at SQL server. The record
> should be inserted into the table by auto truncating any string which exceeds
> the max length being allowed by the table without giving any error. This is
> because for any error that prompts, the app will terminate and no record will
> be inserted.
>
> Hope that I've made my condition clear. Thanks for the guide so far :)
>
>
> "Logicalman" wrote:
>
>> BTLye,
>>
>> I'm not quite with what you are trying to do.
>> You have two choices to either turn off the warning and ignore them, or to
>> avoid the warnings being produced.
>> For the latter (my preference) dependant on how the application is sending
>> the INSERT instruction will depend on how you can proceed.
>> e.g. If you are calling a SProc from the application, and passing params,
>> one of which is the string causing problems, then you can handle this inside
>> the SProc ...
>>
>> ..(start of SProc) ...
>>      @myVal nvarchar(100) = ''
>> AS
>>      SET @myVal  = LEFT(@myVal, 10)
>> ... (Rest of SProc) ...
>>
>>
>> If you are using a direct INSERT statement, then you will need to handle
>> this in the application itself. A point to note is that SProcs can, and do,
>> afford a little extra security to any application/SQL setup, ads they act as
>> middle-ware to ensure data integrity (as above) and also avoid any of the
>> underlaying table schema being visible to the Application side.
>>
>> Tony
>>
>>
>> "BTLye" wrote:
>>
>> > Thanks for the recommendation...But after rule is added...another error
>> > message is given...i might need to just truncate the string without halting
>> > the process...
>> >
>> > "Logicalman" wrote:
>> >
>> > > You will need to establish some rules for the truncation.
>> > >
>> > > The simplest one is to allow only the first 10 characters through. This is
>> > > attained by:
>> > > SELECT LEFT(myColName,10) FROM myTableName
>> > >
>> > > Others may be the right 10 characters (SELECT RIGHT(myColName,10) ..) etc..
>> > >
>> > > If you want a more complex algorithm, then I would suggest that INSERTS and
>> > > UPDATES to the table are handled vis a Stored Procedure, where your rules can
>> > > be effected at much greater depth.
>> > >
>> > > Tony
>> > >
>> > > "BTLye" wrote:
>> > >
>> > > > Hi SQL experts,
>> > > >
>> > > > May I know how can I auto truncate a string to be inserted to a SQL table to
>> > > > avoid from getting the error "String or binary data would be truncated."? For
>> > > > instance, if sent-in data has 15 chars but the column length is only set to
>> > > > 10, the data will be truncated for its last 5 chars.
>> > > >
>> > > > I understand that it is definately better to control this in the application
>> > > > which performs the record insertion. However, due to certain circumstances I
>> > > > might need to control this in SQL server.
>> > > >
>> > > > Any idea would be much appreciated. Thanks :)

Relevant Pages