Solved

sql convert from float to nvarchar

Posted on 2011-03-14
3
1,101 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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