Make column values in a table have equal number of characters

Question

I have a table DomainDetail having a column fieldID.

It has values like A1,B22,A567,D7779,B86759 .. i.e. from two characters to max six characters.

I want these values have equal number of characters A000001,B000022,A000567,D07779 and B86759 .

This is how I think I should proceed

Estimate size of field value = LEN(fieldID)
Insert number of zeros equal to (6 - number of characters) .

How can I insert 0's sandwiched inside original value . How can do in SQL ?

Is it always just one character leading? I mean will you have values like AA1? — Raj, Nov 30 '12 at 08:15
No , first character is ALPHABET , rest are numerals..plz help .. — Mudassir Hasan, Nov 30 '12 at 08:15

score 4 · Accepted Answer · answered Nov 30 '12 at 08:19

4

like this

select 
    left(fieldID, 1) + 
    right('000000' + right(fieldID, len(fieldID) - 1), 5)
from DomainDetail

take a look at SQL FIDDLE example

answered Nov 30 '12 at 08:19

Roman Pekar

107,110
28
195
197

score 2 · Answer 2 · edited May 23 '17 at 11:43

2

It sounds like a problem better solved by business logic, i.e. the layer of code above your database. Whenever you insert, do the padding in that code - then always use that code/layer to insert into the table.

It seems to be a business logic requirement anyway - "ID must have a maximum 6 characters". Because a database wouldn't impose such a limit.

(unless you are using stored procedures as your business logic layer? in which case, PadLeft function in T-SQL)

edited May 23 '17 at 11:43

Community

1
1

answered Nov 30 '12 at 08:14

Kieren Johnstone

41,277
16
94
144

i don't have access to code implementing business logic , only table...plz help – Mudassir Hasan Nov 30 '12 at 08:16
help how? make a suggestion, then research how to do development, and write the code for you? – Kieren Johnstone Nov 30 '12 at 19:32

score 1 · Answer 3 · answered Nov 30 '12 at 08:18

select 
stuff(fieldId,2,0,
      LEFT('0000000000000000000000000',
      (select max(LEN(FieldID)) from DomainDetail)

           -LEN(fieldId)))
from DomainDetail

If you need a fixed output length just replace inner select (select max(LEN(FieldID)) from DomainDetail) with for example 6 Here is a SQLFiddle demo

score 1 · Answer 4 · answered Nov 30 '12 at 08:30

If you want to UPDATE, then use this

UPDATE DomainDetail
SET fieldId=
            SUBSTRING(fieldId,1,1)+
            REPLICATE('0',6-LEN(id))+
            SUBSTRING(fieldId,2,LEN(id)-1)

If you want to just SELECT without altering the values in the table, then this should work

SELECT SUBSTRING(id,1,1)+
       REPLICATE('0',6-LEN(id))+
       SUBSTRING(id,2,LEN(id)-1) 
FROM DomainDetail

Hope this helps,

Raj

score 1 · Answer 5 · answered Nov 30 '12 at 08:53

1

select stuff(fieldid, 2, 0, replicate('0', 6-len(fieldid))) 
from DomainDetail

answered Nov 30 '12 at 08:53

t-clausen.dk

43,517
12
59
92

Make column values in a table have equal number of characters

5 Answers5