Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1143
  • Last Modified:

sql convert from float to nvarchar


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
amillyard
Asked:
amillyard
  • 2
1 Solution
 
Lee SavidgeCommented:
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
 
amillyardAuthor Commented:
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
 
amillyardAuthor Commented:
got it working as follows:

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now