SQL CONVERT STRING

Question

I do struggle with some of these conversions, so I do apologize, I have asked a similar question in the past, but just can't get my head around how to achieve this.

So a value of 50.00 is currently being exported into the following format -

000000000000050000

A value of 25.99 would look like

000000000000025990

This is a 18 character field where any leading characters are padded with a zero.

What I am trying to do is convert that to a 19 character string - still with leading zeros - but the value of 50 or 25.99 is slightly different -

000000000000050000 becomes 0000000000000005000

000000000000025990 becomes 0000000000000002599

Any help would be greatly appreciated.

The answers given below perfectly translate the string to what you want. Remember to alter the field that will contain the new numbers to nvarchar(19) ALTER TABLE tablename ALTER COLUMN somecolumn nvarchar(19) — russ
– russ, Commented Oct 12, 2017 at 11:50

Gordon Linoff · Accepted Answer · 2017-10-12 11:22:24Z

1

You would appear to want:

select '00' + left(str, 17)

This is a very strange format. Perhaps you should consider using numeric/decimal, which can accurately represent the number.

answered Oct 12, 2017 at 11:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xpofer · Accepted Answer · 2017-10-12 11:31:07Z

0

A lot of assumptions go into this answer, but...

SELECT '00'+LEFT(OriginalField, 17)

That would truncate your original 18th character and simply put two more zero's on the front.

The solution is not so simple if you need to potentially round up the 17th character.

answered Oct 12, 2017 at 11:31

xpofer

1721 silver badge8 bronze badges

Collectives™ on Stack Overflow

SQL CONVERT STRING

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related