locked
Casting or Converting Uniqueidentifier data type RRS feed

  • Question

  • User-2006371901 posted

    I have a column named tempID in my database table that i need to assign a nchar or nvarchar.  I also have assigned each record in this table a secondaryID which i am using a random id generated by newid() .  I want to update the tempID with the first 5 characters of the secondaryID for that record:

    update members set tempid = left(cast(secondaryID as nchar),5) where meid >= 0

    ...can't seem to find a way to convert or cast the uniqueidentifier datatype of secondaryID
    error= arithmetic overflow error converting expression to data type nchar
    ??
    Thanks
    Ned

    Thursday, July 11, 2019 9:50 PM

Answers

  • User77042963 posted

    Always to remember to assign a size to your data type instead of using its default. In your code, the default site of nchar is 30, which is shorter that the length of uniqueidentifier  type (36).

    Add a size to nchar should work for you.

    update members

    set tempid = left(cast(secondaryID as nchar(36)),5)

    where meid >= 0

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 12, 2019 1:40 PM

All replies

  • User-719153870 posted

    Hi Norkle,

    Norkle

    error= arithmetic overflow error converting expression to data type nchar

    It seems that you have an overflow error when converting the secondary ID of the uniqueidentifier type to nchar type.

    Unique identifier can be understood as a global unique identifier (GUID), which can be initialized using the newid function to convert string constants into the following form ( xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx), where each x is a hexadecimal number in the range of 0-9 or a-f).

    For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

    I created a members table based on your description and used your code to update the data in the table, but I could not reproduce the problem you said.

    The problem may have arisen within the scope of nchar. Which you can refer to: Here

    If you can provide the structure and data of your members table, it will be very helpful for us to solve the problem.

    (Or you can try to replace the 'nchar' in your code with 'nchar(50)'.)

    Below is the structure and data inserted into my demo table:

    Structure:

    Data:

    declare @i int
    set @i=1
    while @i<=5
    begin
    insert into members values(null,NEWID(),@i)
    set @i+=1
    end
    
    select * from members
    
    update members set tempid = left(cast(secondaryID as nchar),5) where meid >= 0

    Result:

    Best Regard,

    Yang Shen

    Friday, July 12, 2019 1:59 AM
  • User77042963 posted

    Always to remember to assign a size to your data type instead of using its default. In your code, the default site of nchar is 30, which is shorter that the length of uniqueidentifier  type (36).

    Add a size to nchar should work for you.

    update members

    set tempid = left(cast(secondaryID as nchar(36)),5)

    where meid >= 0

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 12, 2019 1:40 PM
  • User-2006371901 posted

    Yang, yes including the DDL for the members table wouldve helped more, as meid is the primary key. Thanks for these insights here.

    Ned

    Friday, July 12, 2019 3:02 PM