Search code examples
sqlsql-servert-sql

Make column values in a table have equal number of characters

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 ?

Solution

  • like this

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

    take a look at SQL FIDDLE example