Solved

sql convert from float to nvarchar

Posted on 2011-03-14
3
1,090 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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now