checking for non numeric values

Question

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

Answer 1

What release of SQL Server are you using?

Tom

Answer 2

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!!

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Thanks Naarasimhma,

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

Regards,

Julie

Answer 7

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