Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8

    Unanswered: pad field length in sql?

    Hi, I have a field in access 2002 that needs to be 4 characters in length. Is there any way I can check the length and pad the field in sql?

  2. #2
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    You can use the LEN() function. With it, you can use the SPACE() function and add

    fld & SPACE( 4 - LEN(fld) )


    JefB

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    concatenate 4 blanks onto the end, then take the left 4 characters

    select left(thefield & space(4), 4)

    rudy

  4. #4
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8
    Originally posted by r937
    concatenate 4 blanks onto the end, then take the left 4 characters

    select left(thefield & space(4), 4)

    rudy
    Actually, I need to pad the field with "0"s. I guess I should have mentioned that before, sorry. The field right now has numbers in it, but it is a text field. Both ways work if I just need spaces. I can't figure out how to do it though seeing as I need that "0" character .

  5. #5
    Join Date
    Oct 2002
    Location
    TN
    Posts
    8
    Originally posted by r937
    concatenate 4 blanks onto the end, then take the left 4 characters

    select left(thefield & space(4), 4)

    rudy
    Actually, I need to pad the field with "0"s. I guess I should have mentioned that before, sorry. The field right now has numbers in it, but it is a text field. Both ways work if I just need spaces. I can't figure out how to do it though seeing as I need that "0" character .

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select right('0000' & thefield, 4)


  7. #7
    Join Date
    Aug 2002
    Location
    Québec, Canada
    Posts
    109
    Is your field that need to be padded a number? If yes, you can use the FORMAT() function:

    select FORMAT("0000", fld), but if your number is greater than 9999, you'll loss datas

    JeffB

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Function LeftPad(strMyString As String, intLength As Integer, strPadCharacter As String) As String
    LeftPad = Right$(String$(intLength, left$(strPadCharacter, 1)) & strMyString, intLength)
    End Function

    Function RightPad(strMyString As String, intLength As Integer, strPadCharacter As String) As String
    RightPad = left$(strMyString & String$(intLength, left$(strPadCharacter, 1)), intLength)
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •