Post reply

updating records from a staging table while converting and removing trailing or leading spaces

  • August 17, 2018 at 7:52 am

    #377636

    I am trying to update a  company ID for a table and running into issues. I am wondering if someone can help me with a query. This is what I am using to update values. I have tried multiple methods but nothing is working. 
    update [PMG].[Contactdata]
    set CompanyNids = convert(int,LTRIM(RTRIM(s.CompanyNid)))
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL

    error

    Conversion failed when converting the nvarchar value '    29840' to data type int.

  • August 17, 2018 at 7:57 am

    #2002240

    It's most likely a leading tab instead of a leading space.  LTRIM/RTRIM will only remove spaces.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 17, 2018 at 8:03 am

    #2002243

    NewBornDBA2017 - Friday, August 17, 2018 7:52 AM

    I am trying to update a  company ID for a table and running into issues. I am wondering if someone can help me with a query. This is what I am using to update values. I have tried multiple methods but nothing is working. 
    update [PMG].[Contactdata]
    set CompanyNids = convert(int,LTRIM(RTRIM(s.CompanyNid)))
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL

    error

    Conversion failed when converting the nvarchar value '    29840' to data type int.

    Try running it as a SELECT:
    SELECT c.CompanyNids cCompanyNids,
       s.CompanyNid sCompanyNid,
       convert(int,LTRIM(RTRIM(s.CompanyNid))) TrimsCompanyNid
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL
    Then you might see what is wrong.

  • August 17, 2018 at 8:28 am

    #2002255

    Jonathan AC Roberts - Friday, August 17, 2018 8:03 AM

    NewBornDBA2017 - Friday, August 17, 2018 7:52 AM

    I am trying to update a  company ID for a table and running into issues. I am wondering if someone can help me with a query. This is what I am using to update values. I have tried multiple methods but nothing is working. 
    update [PMG].[Contactdata]
    set CompanyNids = convert(int,LTRIM(RTRIM(s.CompanyNid)))
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL

    error

    Conversion failed when converting the nvarchar value '    29840' to data type int.

    Try running it as a SELECT:
    SELECT c.CompanyNids cCompanyNids,
       s.CompanyNid sCompanyNid,
       convert(int,LTRIM(RTRIM(s.CompanyNid))) TrimsCompanyNid
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL
    Then you might see what is wrong.

    This is just going to produce the same error.  For troubleshooting, you should change the CONVERT to a TRY_CONVERT and/or use some other method to inspect the problem records.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 17, 2018 at 8:33 am

    #2002258

    Done
    update [Staging].[StgContactdata]
    set CompanyNid = REPLACE(CompanyNid, char(9),'')

    update [PMG].[Contactdata]
    set CompanyNids = convert(int,s.CompanyNid)
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL

  • August 17, 2018 at 8:35 am

    #2002259

    drew.allen - Friday, August 17, 2018 8:28 AM

    Jonathan AC Roberts - Friday, August 17, 2018 8:03 AM

    NewBornDBA2017 - Friday, August 17, 2018 7:52 AM

    I am trying to update a  company ID for a table and running into issues. I am wondering if someone can help me with a query. This is what I am using to update values. I have tried multiple methods but nothing is working. 
    update [PMG].[Contactdata]
    set CompanyNids = convert(int,LTRIM(RTRIM(s.CompanyNid)))
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL

    error

    Conversion failed when converting the nvarchar value '    29840' to data type int.

    Try running it as a SELECT:
    SELECT c.CompanyNids cCompanyNids,
       s.CompanyNid sCompanyNid,
       convert(int,LTRIM(RTRIM(s.CompanyNid))) TrimsCompanyNid
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL
    Then you might see what is wrong.

    This is just going to produce the same error.  For troubleshooting, you should change the CONVERT to a TRY_CONVERT and/or use some other method to inspect the problem records.

    Drew

    Can try this then:
    SELECT c.CompanyNids cCompanyNids,
     s.CompanyNid sCompanyNid,
     LTRIM(RTRIM(s.CompanyNid)) TrimsCompanyNid
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL
    and s.CompanyNid LIKE '%29840%'

  • August 17, 2018 at 8:51 am

    #2002267

    Personally, my preferred method is to convert the string to VARBINARY, particularly since the grid view won't properly display many of the whitespace characters, so it can be difficult to determine exactly which one it is.  Then again, I'm familiar enough with binary encodings of most of the common characters that it's easy for me to pick out which ones might be causing problems.  Others might want to use the ASCII function, but that requires first finding where in the string the problem character is located.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 17, 2018 at 8:55 am

    #2002269

    NewBornDBA2017 - Friday, August 17, 2018 8:33 AM

    Done
    update [Staging].[StgContactdata]
    set CompanyNid = REPLACE(CompanyNid, char(9),'')

    update [PMG].[Contactdata]
    set CompanyNids = convert(int,s.CompanyNid)
    FROM [PMG].[Contactdata] c
    join [Staging].[StgContactdata] s on c.ContactNids = s.ContactNid
    where c.CompanyNids is NULL

    That takes two passed at the table.  Incorporate the REPLACE as the target of the LTRIM/RTRIM in a single pass.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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