Solved

sql convert from float to nvarchar

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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 a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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