Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
checking for non numeric values

Answered checking for non numeric values

  • Friday, February 15, 2013 11:07 PM
     
     
    Sign In to Vote
    0

    I'm trying to  load 97m rows to a different table . One of the field I'm trying to convert is  from varchar(11) to decimal(5,2)

    Field

    Numberofbathrooms varchar(11)

    Values 2.5, 3.10....etc

    There are some dirty data values such  as

    3.5}, 3.5t             So, its not letting me to convert it to decimal(5,2) . How can I check if non-numeric

    values existis and strip them out?

    Any help will be appreciated.

    Julie

     

All Replies

  • Saturday, February 16, 2013 12:16 AM
     
     
    Sign In to Vote
    0

    What release of SQL Server are you using?

    Tom

     
  • Saturday, February 16, 2013 12:41 AM
     
      Has Code
    Sign In to Vote
    1
     
    declare @s table (bof varchar(5))
insert into @s
values ('8.5'),('3.5t')
declare @p table(bofa decimal(5,1))
insert into @p(bofa)
select * from @s where ISNUMERIC(bof)=1
select * from @p

    Hope it Helps!!

     
  • Saturday, February 16, 2013 1:10 AM
    Moderator
     
      Has Code
    Sign In to Vote
    1

    SQL Server 2012 has a revolutionary new solution for your problem: TRY_CONVERT().

    Demo:

    SELECT DecValue = TRY_CONVERT(DECIMAL(5,0), PostalCode), PostalCode
FROM AdventureWorks2012.Person.Address;
/*
DecValue	PostalCode
NULL	K4B 1S2
NULL	V5A 4X1
59140	59140
91370	91370
91370	91370
.....
*/

    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


     
  • Saturday, February 16, 2013 1:23 AM
     
     Answered Has Code
    Sign In to Vote
    1

    You can use ISNUMERIC and PATINDEX for checking characters, trim and then Convert those..

    DECLARE @t TABLE(Nums VARCHAR(11))
INSERT INTO @t(Nums)
VALUES('13'),('3.2}'),('3.5t'),('45.6')
SELECT *
,CASE WHEN ISNUMERIC(Nums)=0 
		THEN CAST(LEFT(Nums,PATINDEX('%[^.0-9]%',Nums)-1) AS DECIMAL(5,2))
	  ELSE CAST(Nums AS DECIMAL(5,2))END AS Converted_Value
FROM @t

    /*
Nums	Converted_Value
13	13.00
3.2}	3.20
3.5t	3.50
45.6	45.60
*/


    Narsimha

    • Marked As Answer by Julie Chang Sunday, February 17, 2013 4:14 AM
    •  
     
  • Saturday, February 16, 2013 6:37 AM
     
     
    Sign In to Vote
    1
    DECLARE @t TABLE(Nums VARCHAR(11))
    INSERT INTO @t(Nums)
    VALUES('13'),('3.2}'),('3.5t'),('45.6')
    select * from @t where ISNUMERIC(nums)=1

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Saturday, February 16, 2013 6:48 PM
     
     
    Sign In to Vote
    0

    Thanks Naarasimhma,

    Your suggestion solved the problem, I couldn't understand what -1 does ?

    Regards,

    Julie

     
  • Saturday, February 16, 2013 7:02 PM
     
     Answered Has Code
    Sign In to Vote
    1

    With PATINDEX, it gives the position of the character like '}' or 't' and using the LEFT function, we will get all the data till that position - 1, since we don't want to include that char while converting to FLOAT.

    Try this -

    DECLARE @t TABLE(Nums VARCHAR(11))
INSERT INTO @t(Nums)
VALUES('13'),('3.2}'),('3.5t'),('45.6')
SELECT *
,PATINDEX('%[^.0-9]%',Nums) AS char_pos
,LEFT(Nums,PATINDEX('%[^.0-9]%',Nums)) AS CharInclude --Includes the chars - },t
,CASE WHEN ISNUMERIC(Nums)=0 THEN LEFT(Nums,PATINDEX('%[^.0-9]%',Nums)-1) END AS CharExclude--with -1,we exclude those chars - },t
,CASE WHEN ISNUMERIC(Nums)=0 
 THEN CAST(LEFT(Nums,PATINDEX('%[^.0-9]%',Nums)-1) AS DECIMAL(5,2))
 ELSE CAST(Nums AS DECIMAL(5,2))END AS Converted_Value
FROM @t


    /*
Nums	char_pos	CharInclude	CharExclude	Converted_Value
13	0		NULL				13.00
3.2}	4		3.2}		3.2		3.20
3.5t	4		3.5t		3.5		3.50
45.6	0		NULL				45.60
*/

    Hope this helps.

    Narsimha

    • Proposed As Answer by DeviantLogic Sunday, February 17, 2013 12:15 AM
    • Unproposed As Answer by Julie Chang Sunday, February 17, 2013 4:12 AM
    • Marked As Answer by Julie Chang Sunday, February 17, 2013 4:12 AM
    •