Professional Web Applications Themes

Raw Column Data to Fields - Microsoft SQL / MS SQL Server

Here is the situation: Table1 col1 ------------------------------------------- FirstNameLastNameMiddleNameStateCode First 9 chars are FirstName Next 8 chars are LastName Next 10 chars are MiddleName StateCode 2 chars This format is fixed. What's the best way to split it into another table table2 col1 col2 col3 col4 --------- ----- ----- ------ FirstName LastName MiddleName StateCode Thanks!!!...

  1. #1

    Default Raw Column Data to Fields

    Here is the situation:
    Table1
    col1
    -------------------------------------------
    FirstNameLastNameMiddleNameStateCode

    First 9 chars are FirstName
    Next 8 chars are LastName
    Next 10 chars are MiddleName
    StateCode 2 chars
    This format is fixed.


    What's the best way to split it into another table

    table2
    col1 col2 col3 col4
    --------- ----- ----- ------
    FirstName LastName MiddleName StateCode

    Thanks!!!









    sunil Guest

  2. #2

    Default Raw Column Data to Fields

    You can use insert statement with substring function or if
    table does not exist you can use select into clause.

    insert into table2 select substring(col1,1,9), substring
    (col1,10,8), substring(col1,19,10),substring(col1,29,2)
    from pitemp

    Ricky
    >-----Original Message-----
    >Here is the situation:
    >Table1
    >col1
    >-------------------------------------------
    >FirstNameLastNameMiddleNameStateCode
    >
    >First 9 chars are FirstName
    >Next 8 chars are LastName
    >Next 10 chars are MiddleName
    >StateCode 2 chars
    >This format is fixed.
    >
    >
    >What's the best way to split it into another table
    >
    >table2
    >col1 col2 col3 col4
    >--------- ----- ----- -----
    -
    >FirstName LastName MiddleName StateCode
    >
    >Thanks!!!
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >.
    >
    Ricky Guest

Similar Threads

  1. Getting column data without column names
    By Jared@Itron in forum Coldfusion Database Access
    Replies: 4
    Last Post: January 18th, 09:26 PM
  2. DataGrid HyperLink Column - How can I put multiple fields in the URL field
    By Alberto Teixeira in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: July 29th, 02:09 PM
  3. combining 2 datasource fields into hyperlink column
    By Ben in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: July 26th, 04:11 PM
  4. Mult Fields in one column
    By Dean in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: September 25th, 03:47 AM
  5. Three fields in one column
    By Antonio Maciel in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: July 4th, 12:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139