sql convert text string from 1 integer (8) to 3 integers (888)

Question

I am having a little difficulty with the below code - it return the value I am after, but I need the value 3 digits long - i.e. it returns '1' but I need '001' - any help would be gratefully received

select convert(varchar(3),(select count(ptMatter) + 1 from lamatter where  
 convert(varchar(10), getdate(), 103)=convert(varchar(10), dateadd, 103)))

Are you trying to repeat the integer three times (question title), or add leading zeros (question body)? Your question is contradictory. — Delan Azabani, Mar 01 '11 at 12:46
possible duplicate of [Most efficient T-SQL way to pad a varchar on the left to a certain length?](http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length) — Gabe, Mar 01 '11 at 12:47

score 4 · Accepted Answer · answered Mar 01 '11 at 12:47

Select Right('000000' + convert(varchar(3), Result),3)
From yourTable

for your exact query:

Select Right('000000' + convert(varchar(3), (select count(ptMatter) + 1 from lamatter where convert(varchar(10), getdate(), 103)=convert(varchar(10), dateadd, 103)) ),3)

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

Look at your database documentation if the lpad function exists. For example, this is the mysql version : http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_lpad

edit: sorry, didn't saw the tsql tag... Like said in another answer, the right function is the way to go, like explained here : Most efficient T-SQL way to pad a varchar on the left to a certain length?

edited May 23 '17 at 12:04

Community

1
1

answered Mar 01 '11 at 12:48

krtek

26,334
5
56
84

score 0 · Answer 3 · answered Mar 01 '11 at 12:49

0

What about something like this:

DECLARE @i int;
SELECT @i = 1;
SELECT REPLACE(STR(@i, 3), ' ', '0')

answered Mar 01 '11 at 12:49

Frank Kalis

1,322
9
8

sql convert text string from 1 integer (8) to 3 integers (888)

3 Answers3