October 30, 2012 at 11:08 am
Hi SQL Server experts,
I am trying to automate a routine of importing some data. I am using the Execute SQL Task in the SSIS. But the question is not about SSIS and is only about T-SQL.
I have a table with one varchar(8) column, in which all rows, except one, are only numeric characters (0-9).
I want to automate this routine so that we can ignore this row which is not numeric, and other possible not convertable rows that may appear in the future.
If I use BEGIN TRY ... BEGIN CATCH, with the CONVERT or CAST functions, the operation fails and I get the row which threw the error, but only one row comes.
Is there any way to ignore the "unconvertable" values for this column? Some kind of Is_Number or IS_NAN function to check if it can be converted to int?
Thanks in advance,
Lucas Benevides
DBA Cabuloso
________________
DBA Cabuloso
Lucas Benevides
October 30, 2012 at 11:24 am
There is an IsNumeric() function in T-SQL, but it's not very reliable.
I find a Where clause like this more useful:
WHERE MyColumn NOT LIKE '%[^0-9]%'
The ^ operator in a Like comparison means "anything other than". So if the string contains any characters that aren't in "0-9", it will be skipped by the Where clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2012 at 11:37 am
Do you think if this wold help ?
CREATE FUNCTION dbo.IsNumericEx( @value nvarchar(max) )
RETURNS BIT
AS
BEGIN
DECLARE @isInt BIT
SELECT @isInt = 1
WHERE @value NOT LIKE '%[^0-9.-]%'
AND LEN(REPLACE(@value, '.', '')) >= LEN(@value) - 1
AND CHARINDEX('-', @value) IN (0, 1)
AND CHARINDEX('-', @value, 2) = 0
RETURN ISNULL(@isInt, 0)
END
GO
DECLARE @t1 TABLE (title varchar(20))
INSERT INTO @t1
SELECT '123d456' UNION ALL
SELECT '12 3456' UNION ALL
SELECT '123456' UNION ALL
SELECT '1234-56' UNION ALL
SELECT '123456-' UNION ALL
SELECT '-123456' UNION ALL
SELECT '-123-456' UNION ALL
SELECT 'dddfaf56' UNION ALL
SELECT '5532.673' UNION ALL
SELECT '5532673.' UNION ALL
SELECT '.5532.673'
SELECT *
FROM @t1
WHERE dbo.IsNumericEx(title) = 0
SELECT *
FROM @t1
WHERE dbo.IsNumericEx(title) = 1
GO
DROP FUNCTION dbo.IsNumericEx
October 30, 2012 at 11:43 am
Thank you both.
I didn't know the function IsNumeric. To my case I think it is enough.
The written function IsNumericEx can be very useful.
Best regards,
Lucas Benevides
DBA Cabuloso.
________________
DBA Cabuloso
Lucas Benevides
October 30, 2012 at 12:08 pm
Keep in mind, "1D2" will return 1 from IsNumeric, but will get an error if you try to convert it to an Integer. That's just one example of how IsNumeric can throw you. ("1D2" is a number in a form of scientific notation, so it is validly a number, but SQL Server can't convert it to most of the numeric data types. Does work with Float though.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply