#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    8
    Rep Power
    0

    Data type problem


    In my oracle database i have a view:
    SELECT REGEXP_SUBSTR(LV.VAN_ID, '[^ ]+', 1, 1) VanNumber,
    REGEXP_SUBSTR(LV.VAN_ID, '[^ ]+', 1, 2) DayName,
    REGEXP_SUBSTR(LV.VAN_ID, '[^ ]+', 1, 3) ShiftName,
    REGEXP_SUBSTR(LV.VAN_ID, '[^ ]+', 1, 4) Warehouse_Code
    FROM LEO_VANS LV
    ORDER BY VanNumber

    Now when it splits the string and puts it into the column the default type is VARCHAR how do i specify that the VanNumber field for example must be an integer.

    Thanks in advanced.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387

    Cool


    Like this:
    Code:
    SELECT 
    CAST (REGEXP_SUBSTR(LV.VAN_ID, '[^ ]+', 1, 1) AS NUMBER) VanNumber,
    -- Etc ---
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    That's the price you pay for having a bad design.

    If you want the substring to be a number, wrap it into a to_number() call:

    Code:
    to_number(REGEXP_SUBSTR(LV.VAN_ID, '[^ ]+', 1, 1) )
    But you might need to deal with errors due to invalid numbers in the character value of van_id.

    Please for future posts, put your SQL code into [code] tags.

    Details are here: http://forums.devshed.com/misc.php?do=bbcode
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    8
    Rep Power
    0
    Cheers all both good solutions

    Many Thanks

IMN logo majestic logo threadwatch logo seochat tools logo