Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: Sorting Numbers Stored As Varchar

    Hi All,

    I have a field in a table that is varchar. The field can contain numbers or alpha characters. I have a view against this table and I am trying to sort on the output of this field and am running into some problems.

    I am trying to use 'isnumeric' to determine if the field contains numbers and if so, then I am using 'convert' to change it to an integer so I can sort it properly. I am using a CASE statement but I am encountering a conversion error on the alpha characters. I am new to SQL and my syntax may be wrong.

    Can anyone help please?

    SELECT TOP 100 PERCENT SpaceName, CASE WHEN isnumeric(RoomTable.RoomNumber) <> 0 THEN CONVERT(int, RoomTable.RoomNumber)
    ELSE RoomTable.RoomNumber END RoomNumber
    FROM dbo.RoomTable
    ORDER BY SpaceName, RoomNumber

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Working from your text description, I'd use:
    Code:
    SELECT SpaceName, RoomNumber
       FROM dbo.RoomTable
       ORDER BY SpaceName
    ,     CASE
             WHEN 0 = IsNumeric(RoomTable.RoomNumber) THEN -2147483648
             ELSE Cast(RoomTable.RoomNumber AS INT) 
          END
    -PatP

  3. #3
    Join Date
    Mar 2008
    Posts
    12
    Thank You.

    I took your sql and just changed the THEN (see below) I still encounter the folowing error:

    "Syntax error converting the varchar value 'A' to a column of data type int"

    One of the RoomNumber values is an A.

    Any thoughts?


    SELECT SpaceName, RoomNumber
    FROM dbo.RoomTable
    ORDER BY SpaceName, CASE WHEN 0 = IsNumeric(RoomTable.RoomNumber) THEN RoomTable.RoomNumber ELSE Cast(RoomTable.RoomNumber AS INT)
    END

  4. #4
    Join Date
    Mar 2008
    Posts
    12
    Thank You, I reviewed the syntax you provided and got it to work with the following:

    SELECT TOP 100 PERCENT SpaceName, RoomNumber
    FROM dbo.RoomTable
    ORDER BY SpaceName, CASE WHEN 1 = IsNumeric(RoomTable.RoomNumber) THEN Cast(RoomTable.RoomNumber AS INT) END

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm sure that I'm just being silly here, but did you try using my query as I posted it? I'd be curious to know what you think of my queries output compared to the output of the query in post #4.

    -PatP

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The optimizer can do some funky things with order of operations. If the table is large, and a tablescan is inevitable, the optimizer may schedule the conversion to integer before the filtering. I saw this in a query with a join. The detail table, referred to a OTLT, and the developer wanted to convert the code in the OTLT to a numeric. Unfortunately, the optimizer decided it was more efficient to convert the whole OTLT table to numeric, before the join.



    EDIT: Ahh, I thought I saved my experiments on this thing
    Code:
    --Create a few tables to work with
    
    create table #test1
    (id int identity(1, 1) not null primary key,
     result1 int not null)
    
    create table #testcodes
    (id int identity(1, 1) not null primary key,
     codegroup int not null,
     value varchar(100))
    
    
    -- insert some data
    insert into #testcodes (codegroup, value)
    select 1, '1'
    union
    select 1, '2'
    union
    select 1, '3'
    union 
    select 2, 'no'
    union 
    select 2, 'yes'
    
    insert into #test1 (result1)
    select 1
    union all
    select 1
    union all
    select 2
    
    -- Queries work just fine, when data volumes are low
    select cast (c.value as int)
    from #test1 t1 join
    	#testcodes c on t1.result1 = c.id
    
    -- Add some more data
    set nocount on
    declare @i int
    set @i = 1
    while @i < 1000
      begin
    	insert into #test1 (result1)
    	select ceiling (3* rand())
    
    	set @i = @i + 1
      end
    
    -- now we have problems
    
    select cast (c.value as int)
    from #test1 t1 join
    	#testcodes c on t1.result1 = c.id
    
    -- Clean up.
    drop table #test1
    drop table #testcodes
    Have a look at the query plans to see where the convert lands in relation to the join/filter.
    Last edited by MCrowley; 03-20-08 at 10:32.

Posting Permissions

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