locked
Error converting data type nvarchar to numeric in SQL Query RRS feed

  • Question

  • User1891508652 posted

    Hi Can someone tell me how to handle this case?

    Below is my query. I'm trying to print output as Special characters first then numerics then alpha numerics.

    CREATE TABLE [Emp](
        [Id] [nvarchar](50) NULL,
        [Name] [varchar](50) NULL
    ) ON [PRIMARY]
    
    GO
    insert into emp values ('01','one')
    insert into emp values ('02','Two')
    insert into emp values ('3','Three')
    insert into emp values ('10','Ten')
    insert into emp values ('01A','oneA')
    insert into emp values ('*','Star')
    go
    declare @sortby varchar(100)
    set @sortby = 'Id'
    select * from emp
    
    ORDER BY 
        CASE @sortby
          WHEN 'Id' THEN 
             CASE 
                WHEN ISNUMERIC(Id)=1 
                    THEN CAST(Id as int)
                WHEN PATINDEX('%[^0-9]%',Id) > 1 
                    THEN CAST(LEFT(Id,PATINDEX('%[^0-9]%',Id) - 1) as int)
                ELSE 2147483648
            END
            else
            CASE 
                WHEN ISNUMERIC(Id)=1 
                    THEN NULL
                WHEN PATINDEX('%[^0-9]%',Id) > 1 
                    THEN SUBSTRING(Id,PATINDEX('%[^0-9]%',Id) ,50) 
                ELSE Id
            END 
        END,
        Name

    And when I try to change @sortBy = 'Name' I'm getting error like "Error converting data type nvarchar to numeric."

     

    Tuesday, December 1, 2015 4:59 AM

Answers

  • User77042963 posted
    CREATE TABLE [Emp](
        [Id] [nvarchar](50) NULL,
        [Name] [varchar](50) NULL
    ) ON [PRIMARY]
    
    GO
    insert into emp values ('01','one')
    insert into emp values ('02','Two')
    insert into emp values ('3','Three')
    insert into emp values ('10','Ten')
    insert into emp values ('01A','oneA')
    insert into emp values ('*','Star')
    go
    declare @sortby varchar(100)
    set @sortby = 'Name'
    select * from emp
    
    ORDER BY
        CASE @sortby
          WHEN 'Id' THEN
             CASE
                WHEN ISNUMERIC(Id)=1
                    THEN CAST(Id as int)
                WHEN PATINDEX('%[^0-9]%',Id) > 1
                    THEN CAST(LEFT(Id,PATINDEX('%[^0-9]%',Id) - 1) as int)
                ELSE 2147483648
            END
       End,
            CASE @sortby
          WHEN 'Name' THEN
               Case WHEN ISNUMERIC(Id)=1
                    THEN NULL
                WHEN PATINDEX('%[^0-9]%',Id) > 1
                    THEN SUBSTRING(Id,PATINDEX('%[^0-9]%',Id) ,50)
                ELSE Id
            END
    		End
    ,  Name
    
    
    	drop table Emp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 1, 2015 2:39 PM

All replies

  • User603616845 posted

    Hi,

    Error converting data type nvarchar to numeric.

    It is because you are forcefully trying to convert your varchar type to int. See the following code, in this code you have declared @sortby as varchar,

    declare @sortby varchar(100)
    set @sortby = 'Id'

    but in the below code you are trying to convert it in int. See the below bold line.

     WHEN ISNUMERIC(Id)=1
                    THEN CAST(Id as int)

    So, make sure casting is doing in right way.

    Hope this will help  you.

    thanks

    Tuesday, December 1, 2015 5:07 AM
  • User1891508652 posted

    then how can we resolve this? my column is varchar only.

    Tuesday, December 1, 2015 12:04 PM
  • User77042963 posted
    CREATE TABLE [Emp](
        [Id] [nvarchar](50) NULL,
        [Name] [varchar](50) NULL
    ) ON [PRIMARY]
    
    GO
    insert into emp values ('01','one')
    insert into emp values ('02','Two')
    insert into emp values ('3','Three')
    insert into emp values ('10','Ten')
    insert into emp values ('01A','oneA')
    insert into emp values ('*','Star')
    go
    declare @sortby varchar(100)
    set @sortby = 'Name'
    select * from emp
    
    ORDER BY
        CASE @sortby
          WHEN 'Id' THEN
             CASE
                WHEN ISNUMERIC(Id)=1
                    THEN CAST(Id as int)
                WHEN PATINDEX('%[^0-9]%',Id) > 1
                    THEN CAST(LEFT(Id,PATINDEX('%[^0-9]%',Id) - 1) as int)
                ELSE 2147483648
            END
       End,
            CASE @sortby
          WHEN 'Name' THEN
               Case WHEN ISNUMERIC(Id)=1
                    THEN NULL
                WHEN PATINDEX('%[^0-9]%',Id) > 1
                    THEN SUBSTRING(Id,PATINDEX('%[^0-9]%',Id) ,50)
                ELSE Id
            END
    		End
    ,  Name
    
    
    	drop table Emp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 1, 2015 2:39 PM
  • User1891508652 posted

    Thanks limno it is workingCool

    Tuesday, December 1, 2015 4:02 PM
  • User1891508652 posted

    Hey Limno one more question how can I get special characters first in that output? I mean * should come first in the output

    Tuesday, December 1, 2015 4:37 PM
  • User77042963 posted
    declare @sortby varchar(100)
    set @sortby = 'Id'
    select * 
     from emp
    
    ORDER BY
        CASE @sortby
          WHEN 'Id' THEN
             CASE
    		 When Id='*' Then 0
                WHEN ISNUMERIC(Id)=1
                    THEN CAST(Id as int)
                WHEN PATINDEX('%[^0-9]%',Id) > 1
                    THEN CAST(LEFT(Id,PATINDEX('%[^0-9]%',Id) - 1) as int)
                ELSE 2147483648
            END
       End,
            CASE @sortby
          WHEN 'Name' THEN
               Case WHEN ISNUMERIC(Id)=1
                    THEN NULL
                WHEN PATINDEX('%[^0-9]%',Id) > 1
                    THEN SUBSTRING(Id,PATINDEX('%[^0-9]%',Id) ,50)
                ELSE Id
            END
    		End
    ,  Name
    

    Tuesday, December 1, 2015 5:27 PM