Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Smile Unanswered: is this possible? ...not an SQL master

    Hi,

    I am trying to figure out the best way to reformat the record entries in a database.

    In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

    Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

    I have attached a copy of a screenshot with some notes.

    Thanks in advance to anyone who sees the easy way to do this!

    cheers,
    Rick
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: is this possible? ...not an SQL master

    An example:

    INSERT INTO newtable (county, route, px_back, backpm)
    SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

    Assumes that the character is always the last position and length of 1.


    Originally posted by entangled
    Hi,

    I am trying to figure out the best way to reformat the record entries in a database.

    In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

    Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

    I have attached a copy of a screenshot with some notes.

    Thanks in advance to anyone who sees the easy way to do this!

    cheers,
    Rick

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: is this possible? ...not an SQL master

    HI,

    try to use decode(substr(backPM,length(backPM)-1,1),'R',substr(backPM,1,length(backPM)-1,backPM)

    and use the same formula for AheadPM column



    Originally posted by entangled
    Hi,

    I am trying to figure out the best way to reformat the record entries in a database.

    In the source data table on the server, all field types have been defined as 'text' (for some reason), and I need to pull these data out and create a new table with appropriate datatype definitions for the fields.

    Also, two fields are mixed alpha-numeric, while there should really be separate fields for the alpha values.

    I have attached a copy of a screenshot with some notes.

    Thanks in advance to anyone who sees the easy way to do this!

    cheers,
    Rick

  4. #4
    Join Date
    Feb 2004
    Posts
    2

    Re: is this possible? ...not an SQL master

    Thank you for the example. This one example pretty much addresses both issues. I will work with this and see how I can apply this approach.

    many thanks,
    Rick Sperling


    Originally posted by dmmac
    An example:

    INSERT INTO newtable (county, route, px_back, backpm)
    SELECT county, integer(route), CASE WHEN SUBSTR('098',LENGTH('098')) > 'A' THEN SUBSTR('098', 1, LENGTH('098') -1) ELSE null END, CASE WHEN SUBSTR('098R',LENGTH('098R')) > 'A' THEN SUBSTR('098R', LENGTH('098R')) ELSE null END FROM oldtable

    Assumes that the character is always the last position and length of 1.

Posting Permissions

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