CODEWITHSUNDEEP
sqlsql-server-2005t-sql
g.l.gyani
g.l.gyani

Reputation: 11

how to convert string format

How can I convert a number to a formatted string of fixed length in SQL Server 2005 using T-SQL?

e.g.

Inputs: 5,01,007,0009,00011,01200
Result: 000005,000007,000009,0000011,001200

Upvotes: 1

Views: 1957

Answers (5)

DeveloperX
DeveloperX

Reputation: 4683

declare @i int
set @i=10
print replace(str(@i),' ','0')

Upvotes: 0

jack.mike.info
jack.mike.info

Reputation: 128

Best way for dynamic leading zero allocation

WITH t(c) AS ( SELECT 1.99 UNION ALL SELECT 21.34 UNION ALL SELECT 1797.94 UNION ALL SELECT 300.36 UNION ALL SELECT 21.99 UNION ALL SELECT 2.31 ),

Final (a,b,c,d) as ( Select c, substring(cast(c as varchar(10)),1,charindex('.',c)-1) , (select max(len(substring(cast(c as varchar(10)),1,charindex('.',c)-1))) from t), substring(cast(c as varchar(10)),charindex('.',c)+1,len(c)) From t group by c )

select a, right(replicate('0',c)+''+b,4)+'.'+d from final

Upvotes: 0

jack.mike.info
jack.mike.info

Reputation: 128

TRY THIS

WITH t(c) AS
(
SELECT 1.99 UNION ALL 
SELECT 21.34  UNION ALL 
SELECT 1797.94  UNION ALL 
SELECT 300.36  UNION ALL 
SELECT 21.99  UNION ALL 
SELECT -2.31    
)

select 
c,

replicate(0,4-len(replace(substring(cast(c as varchar(10)),1,charindex('.',c)-1),'-','')))+''+
replace(replace(substring(cast(c as varchar(10)),1,charindex('.',c)-1),'-',''),'','-') +''+
replace(substring(cast(c as varchar(10)),charindex('.',c),len(c)),'-','')

from t

i will still optimize it

Upvotes: 0

David Duffett
David Duffett

Reputation: 3145

The best way I've found to do this is using the STR statement:

SELECT REPLACE(STR(123, 6), ' ', '0')

The above statement will result in 000123. It basically converts 123 to a string of 6 characters (padded with spaces), then uses REPLACE to replace the spaces with zeros.

Upvotes: 4

p.campbell
p.campbell

Reputation: 100577

Looks like you want it 6 wide. Try putting your pad characters, in this case, zeros, to the left of your int/string, and then take the 6 chars on the right side of the string.

How about this?

DECLARE @i int;
SELECT @i = 1200;
SELECT RIGHT('000000'+ CAST(@i as varchar(10)), 6);

Upvotes: 5

Related Questions