Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2010
    Location
    Melbourne, Australia
    Posts
    13
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Converting text to numbers for limited rows

    I have a table in SQL Server, which has two columns one of which contains
    • numbers as characters (eg. 214.67)
    • the letter U
    • NULL
    I need to populate the second column with
    • conversion of character numbers to numbers
    • zero if the other field contains the letter U
    • 999,999 if the other field is NULL
    I can select only those records which contain numbers as characters

    select field1 from Table
    where
    (field1 is not null
    and field1 <>'U')

    however when I use this in the update I get an error.

    UPDATE Table
    SET Field2 = val(Field1)
    where
    (field1 is not null and field1 <> 'U')

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    There is no val function in SQL Server. You need to use CAST or CONVERT.

Posting Permissions

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