Post reply

Field length mismatch

  • December 30, 2009 at 8:32 am

    #218996

    I have a source table 'Codes'

    CREATE TABLE [dbo].[Codes](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [Name] [nvarchar](255) NULL,

    [NumCode] [float] NULL,

    [PhoneCode] [varchar](4) NULL

    ) ON [PRIMARY]

    I populated this table with data from an Excel spreadsheet.

    Now, I need to insert data from source table Codes to destination tables CC_Codes

    CREATE TABLE [dbo].[CC_Codes](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [numCode] [varchar](3) NULL,

    [PhoneCode] [smallint] NULL

    PRIMARY KEY CLUSTERED

    I am using the SQL below

    insert into dbo.cc_codes (numcode,phonecode)

    select numcode,cast(rtrim(ltrim(phonecode)) as smallint) from dbo.codes

    I get errors during the insert like

    Msg 245, Level 16, State 1, Line 2

    Conversion failed when converting the varchar value '54 ' to data type smallint.

    I have run a query below, with results shown

    Query select top 3 * from dbo.codes

    where LEN(rtrim(ltrim(phonecode))) = 3

    Result 11ARGENTINA 3254 

    13ARUBA 533297

    14AUSTRALIA 3661 

    I fail to understand why a value 54 appears when len specified is 3. Also, even after using cast and trim functions my insert fails.

    What am I missing...?:unsure:

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • December 30, 2009 at 8:42 am

    #1097490

    It means it's not a space in the final position. Might be a tab, or something similar.

    Select the ASCII value of the third character, look that up, you'll find what it is.

    See if that helps.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • December 30, 2009 at 8:58 am

    #1097502

    Thanks Gsqaured..I ran the query below for the record with phonecode = 54 for which I got the error as

    select ASCII(substring(phonecode,3,1)) from dbo.Codes

    where id = 11

    And the result was 160 which is character á, also it is present on all other rows in the table..

    Any ideas how I can fix it...?

    Thanks..

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • December 30, 2009 at 9:12 am

    #1097512

    You can try something like this...

    DECLARE @phonecode VARCHAR(100)

    SELECT @phonecode = '54' + CHAR(160)

    SELECT LEN(@phonecode)

    SELECT @phonecode = REPLACE(@phonecode,CHAR(160),'')

    SELECT LEN(@phonecode)

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • December 30, 2009 at 9:36 am

    #1097527

    If you are sure that all the extra characters are an ascii code 160 then you can use replace before the trim like this.

    insert into dbo.cc_codes (numcode,phonecode)

    select numcode,cast(rtrim(ltrim(replace(phonecode,char(160),''))) as smallint) from dbo.codes

    If you need to remove all non-numeric characters then you can write a function to remove them before inserting.

    help this helps

  • December 30, 2009 at 9:41 am

    #1097530

    char 160 in a lot of fonts looks like a space; if you use it in an html document, multiple char(160)'s in a row get respected as valid chars to maintian spacing, where multi char(32)'s get merged to a single char.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 30, 2009 at 9:58 am

    #1097538

    Thanks jcdyntek. It works well for me.

    Lowell, I checked all the chars to be Ascii 160, so I am good there. Thanks for bringing it to my attention though..

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • December 31, 2009 at 2:57 am

    #1097799

    '54 '

    There is an extra space after 54

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply