Filter out a field with letters and numbers

Question

I have a update which is for a numerical field.  But the field is now mixed with numbers and letters.

update Table
set NumberField =case when NumberField > 10000 then right(NumberField ,5)
                        when NumberField > 1000 then right(NumberField ,4)
                        when NumberField > 100 then right(NumberField ,3) end

Which errors out now because the field now has numbers and letters such as  SA23089.

What filter would I use for the update to only occur when the field is solely numbers.

where NumberField =  numbers???

Answer 1

where isnumeric(NumberField) = 1

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

You can add an extra level and check IsNumeric first:

update Table 
set NumberField =	case 
						When ISNUMERIC(NumberField) = 1 Then
							Case
								when NumberField > 10000 then right(NumberField ,5) 
								when NumberField > 1000 then right(NumberField ,4) 
								when NumberField > 100 then right(NumberField ,3)
							end
					End

Be aware though that the ISNUMERIC function can act a little wonky.  Sometime not too long ago there was a thread about this.

Also, I'm not sure of your situation, but you may want to evaluate why you can now have alpha characters in a field you previously expected only numbers to be in.

Answer 3

What is the length of this column?

Try:

update T
set ...
where not_a_number_field not like '%^[0-9]%';

---

AMB

Some guidelines for posting questions...

Answer 4

You can use a scalar UDF function to strip out non numeric values.

Here is a sample UDF function:

Create FUNCTION [dbo].[GetNumFromMixed]
(
	@s varchar(100)
)
RETURNS  BIGINT

AS
BEGIN
Declare @myInt BIGINT

declare @pos int

;with mycte as

(select @s as numCol, PATINDEX('%[^0-9]%', UPPER(@s)) pos

union all

Select cast(REPLACE(numCol, SUBSTRING(numCol, pos, 1), '') as varchar(100)) numCol,

PATINDEX('%[^0-9]%', REPLACE(numCol, SUBSTRING(numCol, pos, 1), '')) pos from mycte WHERE mycte.pos>0

)

SELECT @myInt=numCol from mycte WHERE pos=0
RETURN (@myInt);	

END

----Your update
update test 
set NumberField= [dbo].[GetNumFromMixed](NumberField) 

Answer 5

Problems with ISNUMERIC:

http://forums.asp.net/t/1609030.aspx/1

http://www.sql-server-performance.com/forum/threads/problem-with-isnumeric.11181/

SQL Server 2012 TRY_CONVERT to the rescue!

SELECT TRY_CONVERT(INT,PostalCode) AS ZIP, PostalCode
FROM AdventureWorks2012.Person.Address
ORDER BY AddressID;
/*
ZIP	PostalCode
....
4655	4655
98036	98036
NULL	B29 6SL
98284	98284
NULL	V0
91910	91910
91502	91502
97071	97071
...
*/

---

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