Results 1 to 5 of 5

Thread: Parse Field

  1. #1
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parse Field

    I have a field (Codes) in my SQL database that outputs a string of different codes. For example, ABCDEFGHI. For reasons out of my control, we could not store the data like so ABC,DEF,GHI. Is it possible to SELECT that field in the table and output as follows:

    Codes
    ABC,DEF,GHI

    Any insight is much appreciated!

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    What database is that? SQL Server? The length of the field is 9 chars and you want groups of 3?

  3. #3
    Star Lounger
    Join Date
    Jun 2009
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,
    It's a Microsoft SQL Server Database. The max field length is 15 (up to 5, 3 character codes), although, it's not always a guarantee all 5 codes will be entered for each record.

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You have several options to get the characters you need. You have the LEFT function, the RIGHT function, which you can combine, or even the SUBSTRING function. One example of using them could be:

    SELECT LEFT(MyCodeField,3), LEFT(RIGHT(MyCodeField,12),3), SUBSTRING(MyCodeField,7,3) FROM ...

    This would select the first 3 chars, the next 3 chars and chars from position 7 to 9 in the string... Think you get the idea.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Here's a UDF you can use to accomplish the task by just calling it with the value of the field Codes:
    Code:
    Public Function zFmtCodes(zCodes As String) As String
    
        Dim lLen As Long
        Dim lCnt As Long
            
        lLen = Len(zCodes)
        
        For lCnt = 1 To lLen Step 3
           zFmtCodes = zFmtCodes & IIf(lCnt > 1, ",", "") & Mid(zCodes, lCnt, 3)
        Next lCnt
        
    End Function
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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