SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Casting varchar as an integer in SQL Server

    I have a field, current price, that is of datatype varchar that holds the price of a product. It is var char so the customer can enter a range of prices or text such as FREE as well as the actual price. This works great for the system accept when the products are sorted on price as it sorts them as strings instead of numbers so when sorting for least to greates 1110 comes before 22.

    Is there a way to cast the currentPrice column as an int and any field that contains something that is non numeric to return Null so the products will sort properly?

    Thanks
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    order
        by case when isnumeric(currentprice)
                then cast(currentprice as integer)
                else null
            end
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well i seem to be havign some problems... Here is my query


    SELECT p.ProductNumber,p.Manufacturer,p.Model,p.Year,p.productDesc, p.productNumber AS uniqueProductNumber,
    p.productName AS custSiteCatalogProductName, p.image, p.imagelinkURL, p.thumbnail, p.SaleStart, p.SaleEnd,
    CASE WHEN Getdate() >= p.SaleStart
    AND GetDate() <= p.SaleEnd
    THEN p.saleprice
    ELSE p.CurrentPrice
    END AS CurrentPrice,

    CASE RTRIM(CurrentPrice) WHEN ISNUMERIC(RTRIM(CurrentPrice)) THEN
    CAST(RTRIM(CurrentPrice) as float)
    ELSE NULL
    END as sortbyprice
    The error I get is

    Syntax error converting the varchar value '$5,250.00' to a column of data type int.

    The $5,250.00 is what comes out of the varchar field in the database. The field holds a list of VARCHAR values that are dollar amounts or text such as "FREE". What I want is to create column that contains an integer representation of the varchar field if it is number and if it is text, then the value should be NULL. I then want to sort the table on the created column.

    It seems like it is having trouble in the IsNumeric function in the second CASE. Any ideas?

    I started off with your suggestion above by putting the code in the order by but would get a similar error.
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your second CASE is

    CASE foo WHEN isnumeric(foo) THEN ...

    that form of the syntax will end up comparing foo to isnumeric(foo)

    will they ever be equal? no, since isnumeric() yields true or false

    presumably you should have got the ELSE NULL for every row, so right no i can't tell you why it tried to do the conversion

    you want CASE WHEN isnumeric(foo) THEN...

    also, you threw in RTRIM there, so you might as well go whole hog and REPLACE() out the dollar sign, commas, and periods as well
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    update: isnumeric() returns 0 or 1

    i cannot get the darned thing to cast as float!!

    it casts as money, no problem

    Code:
    create table isnumerics
    ( id integer not null identity
    , txtfld varchar(11) 
    )
    
    insert into isnumerics (txtfld) values ( '1' )
    insert into isnumerics (txtfld) values ( '937' )
    insert into isnumerics (txtfld) values ( '937.0' )
    insert into isnumerics (txtfld) values ( '$937' )
    insert into isnumerics (txtfld) values ( '$937.00' )
    insert into isnumerics (txtfld) values ( 'free' )
    insert into isnumerics (txtfld) values ( '.50' )
    insert into isnumerics (txtfld) values ( '1,000' )
    insert into isnumerics (txtfld) values ( '' )
    
    select id
         , txtfld
         , isnumeric(txtfld)
      from isnumerics
      
    1	1	1
    2	937	1
    3	937.0	1
    4	$937	1
    5	$937.00	1
    6	free	0
    7	.50	1
    8	1,000	1
    9		0
    
    
    select id
         , txtfld
         , isnumeric(txtfld)
         , case when isnumeric(txtfld) = 1
                    then cast(txtfld as money)
                    else cast(null as money)
             end as case1
      from isnumerics
    
    1	1	1	1.0000
    2	937	1	937.0000
    3	937.0	1	937.0000
    4	$937	1	937.0000
    5	$937.00	1	937.0000
    6	free	0	
    7	.50	1	.5000
    8	1,000	1	1000.0000
    9		0	  
    
    select id
         , txtfld
         , isnumeric(txtfld)
         , case isnumeric(txtfld)
                    when 1
                    then cast(txtfld as money)
                    else cast(null as money)
             end as case2
      from isnumerics
      
    1	1	1	1.0000
    2	937	1	937.0000
    3	937.0	1	937.0000
    4	$937	1	937.0000
    5	$937.00	1	937.0000
    6	free	0	
    7	.50	1	.5000
    8	1,000	1	1000.0000
    9		0	  
    
    select id
         , txtfld
         , isnumeric(txtfld)
         , case isnumeric(txtfld)
                    when 1
                    then cast(txtfld as float)
                    else cast(null as float)
             end as case2
      from isnumerics
      
    1	1	1	1.0
    2	937	1	937.0
    3	937.0	1	937.0
    that's it!! it just stops!!

    it's gotta be the dollar signs, so don't use float, use money

    either that, or edit out the dollar signs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Jan 2002
    Location
    Omaha, NE
    Posts
    281
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the float was half my problem...I got it to work with your advice. Thank you. I had the RTrim's in there because there were some trailing spaces that I thought were screwing up the conversion so I tried to trim those out. I started from scratch and used the template you have above with "as money" and got what I needed! Thanks!
    "Oh, you hate your job? Why didn't you say so? There's a support group for that.
    It's called EVERYBODY, and they meet at the bar."

    --Drew Carey


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
  •