Wednesday, February 27, 2013 8:32 PM
I have a update which is for a numerical field. But the field is now mixed with numbers and letters.
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???
Wednesday, February 27, 2013 8:40 PM
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
- Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Thursday, March 07, 2013 1:24 AM
Wednesday, February 27, 2013 8:44 PM
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.
Wednesday, February 27, 2013 8:44 PMModerator
What is the length of this column?
where not_a_number_field not like '%^[0-9]%';
Wednesday, February 27, 2013 8:45 PMModerator
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)
Thursday, March 07, 2013 1:31 AMModerator
Problems with ISNUMERIC:
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