Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.6k views
in Technique[技术] by (71.8m points)

string - PadLeft function in T-SQL

I have the following table A:

id
----
1
2
12
123
1234

I need to left-pad the id values with zero's:

id
----
0001
0002
0012
0123
1234

How can I achieve this?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I believe this may be what your looking for:

SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0') 

FROM tableA

or

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]

FROM tableA

I haven't tested the syntax on the 2nd example. I'm not sure if that works 100% - it may require some tweaking - but it conveys the general idea of how to obtain your desired output.

EDIT

To address concerns listed in the comments...

@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.

@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:

SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA

this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...

So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...