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
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
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)
Member
18 Points
158 Posts
Casting or Converting Uniqueidentifier data type
Jul 11, 2019 09:50 PM|Norkle|LINK
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:
<div class="yj6qo"></div> <div class="adL"></div>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
Contributor
2860 Points
910 Posts
Re: Casting or Converting Uniqueidentifier data type
Jul 12, 2019 01:59 AM|Yang Shen|LINK
Hi Norkle,
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:
Result:
Best Regard,
Yang Shen
All-Star
123178 Points
9995 Posts
Moderator
Re: Casting or Converting Uniqueidentifier data type
Jul 12, 2019 01:40 PM|limno|LINK
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
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Member
18 Points
158 Posts
Re: Casting or Converting Uniqueidentifier data type
Jul 12, 2019 03:02 PM|Norkle|LINK
Yang, yes including the DDL for the members table wouldve helped more, as meid is the primary key. Thanks for these insights here.
Ned