Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
keesvo
Starting Member
10 Posts |
 Posted - 2015-02-16 : 04:13:29
|
| Hello,I need a query that gives a result with a 0 in front of values in a certain column (which is a Varchar30 column).For Example:12 - must be: 000012112 - must be: 0001121112 - must be: 001112Can someone help me to figure this one out.Thank you in advance.Kees |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-02-16 : 04:31:47
|
| [code]WITH cteSampleAS (SELECT 12 iValue UNION ALL SELECT 112 UNION ALL SELECT 1112)SELECT iValue , REPLACE(STR(iValue,6),' ','0') as vcValueFROM cteSample [/code]sabinWeb MCP |
 |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-02-16 : 04:34:16
|
| and in SQL 2012 , you can use FORMAT :FORMAT(iValue ,'000000')sabinWeb MCP |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-02-16 : 04:35:51
|
| also this can be used:RIGHT('000000' + CONVERT(VARCHAR(30), iValue), 6)sabinWeb MCP |
|
|
|
keesvo
Starting Member
10 Posts |
Posted - 2015-02-16 : 04:59:50
|
| The first solution did the trick for me !thanks a lot ! |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-02-16 : 05:00:30
|
| Welcome!sabinWeb MCP |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2015-02-16 : 05:42:47
|
| Where are you showing the result set? If it is a front end application, do this formation thereMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
How to place 0 in front of value