Solved

sql convert from float to nvarchar

Posted on 2011-03-14
3
1,099 Views
Last Modified: 2012-05-11

Original SQL Table
[longnumber]   float

want to be able to convert to

[longnumber] nvarchar[25]

some of the numbers start with a '0' (zero) and consequently those entries 1 char less in length that the other db entries for that column.  

how do I convert from the float to the nvarchar[25] and add the missing '0''s where applicable ?

when converting (by simply changing in design view within SQL, the number changes to a maths representation, the number is lost .. although a number -- is actually a text field.
0
Comment
Question by:amillyard
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 35130533
If you want leading zeros, for example:

00025
01234
00001

So a 5 digit number with leading zeros.

Then use this:

select '00000' + right(cast(myvalue as nvarchar(5)) , 5)

0
 

Author Comment

by:amillyard
ID: 35132919
UPDATE db_table
SET [nvarcharNumber] = '0000000000000' + right(cast([nvarcharNumber] as nvarchar(13)), 13)


cannot update db -- get the following scripting error:

Msg 8152, Level 16, State 13, Line 3
String or binary data would be truncated.
The statement has been terminated.
0
 

Author Closing Comment

by:amillyard
ID: 35133030
got it working as follows:

SET [nvarcharNumber] = RIGHT('000000000000' + RTRIM([nvarcharNumber]), 12)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question