November 7, 2011 at 9:42 am
I have a column which is varchar datatype but has data as 2.5, 10.8 and so forth. when i check if thsi column has a value between 0 and 26 i am getting the above conversion error. sql i am using is
and ((CONVERT(decimal(10,3),c.col) >0) or (CONVERT(decimal(10,3),c.col) < 26) . tried a few combinations but for some specific data with decimals it throws an error. any inputs on this wil lbe gratly appreciated.
TIA
November 7, 2011 at 10:34 am
Are you sure there is not bad data in that column?
I was able to blow up your convert WHERE clause with a space and non numeric data.
-- Database selection does not matter
USE model;
GO
-- Create local temp table
CREATE TABLE #T1
(
MyId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
MyValue VARCHAR(20)
)
GO
-- Add data to local temp table
INSERT INTO #T1 (MyValue) VALUES
(2.5),
(10.8)
GO
-- Works with valid data
SELECT * FROM #T1
WHERE ((CONVERT(decimal(10,3), MyValue) > 0) or (CONVERT(decimal(10,3), MyValue) < 26))
GO
-- Add bad data
INSERT INTO #T1 (MyValue) VALUES
('')
GO
INSERT INTO #T1 (MyValue) VALUES
(NULL)
GO
INSERT INTO #T1 (MyValue) VALUES
('3.4.4')
GO
-- Remove the table
DROP TABLE #T1
John Miner
Crafty DBA
www.craftydba.com
November 9, 2011 at 8:11 am
yes the column i use do have null values . so in such a situation is there a solution to this? Any help regarding this will be gratly appreciated. TIA
November 9, 2011 at 8:29 am
I wouldn't say your table has bad data.
The datatype is varchar, which indicates it can hold al lot of characters other than Numbers.
Check your table for non numeric values.
SELECT *
FROM Mytable
WHERE Isnumeric(c.col) = 0
This should help you identify where there is non numeric data.
Inversly when you apply the logic you did before your query can/should have the Isnumeric check in the Where clause.
SELECT *
FROM Mytable
WHERE ISNUMERIC(c.Col) = 1
If you want to continue to store numeric data in a character datatype you will have problems.
I may be off base can you post examples of the decimal combinations that throw errors, and give us the specific error?
however if the values are null it should not throw an error.
November 9, 2011 at 8:59 am
Before you even think of using ISNUMERIC for such a task, please read the following article for why that might not be such a good idea.
http://www.sqlservercentral.com/articles/IsNumeric/71512/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2011 at 3:14 pm
Nice article Jeff. The function that you wrote determines if it is a integer. If there is decimal data, the regular expression has to be changed. Unfortunately, SQL Server does not allow zero or one time operators in the pattern that you can use in PERL or AWK.
Do you have a function to test for decimal numbers?
John Miner
Crafty DBA
www.craftydba.com
November 16, 2011 at 5:10 am
j.miner (11/15/2011)
Nice article Jeff. The function that you wrote determines if it is a integer. If there is decimal data, the regular expression has to be changed. Unfortunately, SQL Server does not allow zero or one time operators in the pattern that you can use in PERL or AWK.Do you have a function to test for decimal numbers?
If you're looking for a simple 10.3 pattern, a combination of LIKEs would do the trick and would, of course, depend on whether it was a fixed-field format or if it was ragged-right.
Ragged-right (works for fixed-field, as well but doesn't check for length, which could be added depending)
WHERE SomeColumn LIKE NOT '%[^0-9.]%' AND SomeColumn LIKE '%[0-9].[0-9][0-9][0-9]'
Fixed-Field
WHERE SomeColumn LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].[0-9][0-9][0-9].
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2011 at 10:43 am
Hi Jeff,
I know you are a big fan of Tally tables and I have not used them since you gave a talk a long time ago at SNESSUG.
Therefore, I took a WAG at a IS_INTEGER and IS_DECIMAL functions using a tally table to parse the strings into individual characters in a table.
Then, I use relational algebra (SELECT) for figure out the answer instead of regular expressions.
I hope you like.
John
Chapter President
SNESSUG
-- Place functions in model
USE model
GO
--
-- usp_Is_Integer - returns 1 if valid integer; otherwise 0
--
-- Drop function if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Is_Integer]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[usp_Is_Integer]
GO
-- Create the function
CREATE FUNCTION dbo.usp_Is_Integer (@str_Buff VARCHAR(128))
RETURNS INT
AS
BEGIN
-- Local variables
DECLARE @tbl_Temp TABLE
(
MyIndex INT,
MyChar CHAR
);
DECLARE @int_Return INT;
-- Remove spaces before & after
SET @str_Buff = LTRIM(RTRIM(@str_Buff));
-- Parse into table by chars using tally
INSERT INTO @tbl_Temp
SELECT N AS MyIndex, SUBSTRING(@str_Buff, N, 1) AS MyChar
FROM
(
SELECT top 128 row_number() over(order by a.name) AS N
FROM sys.columns a CROSS JOIN sys.columns b
) Tally
WHERE N <= LEN(@str_Buff);
-- Must just be 1 to 9
SELECT @int_Return =
CASE WHEN BadChars.Total > 0 THEN 0 ELSE 1 END
FROM
(
SELECT COUNT(*) AS Total
FROM @tbl_Temp AS T
WHERE T.MyChar NOT LIKE '[0-9]'
) BadChars;
-- Return the results
RETURN @int_Return;
END
GO
--
-- usp_Is_Decimal - returns 1 if valid decimal; otherwise 0
--
-- Drop function if it exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Is_Decimal]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[usp_Is_Decimal]
GO
-- Create the function
CREATE FUNCTION dbo.usp_Is_Decimal (@str_Buff VARCHAR(128))
RETURNS INT
AS
BEGIN
-- Local variables
DECLARE @tbl_Temp TABLE
(
MyIndex INT,
MyChar CHAR
);
DECLARE @int_Other_Cnt INT;
DECLARE @int_Decimal_Cnt INT;
DECLARE @int_Return INT;
-- Remove spaces before & after
SET @str_Buff = LTRIM(RTRIM(@str_Buff));
-- Parse into table by chars using tally
INSERT INTO @tbl_Temp
SELECT N AS MyIndex, SUBSTRING(@str_Buff, N, 1) AS MyChar
FROM
(
SELECT top 128 row_number() over(order by a.name) AS N
FROM sys.columns a CROSS JOIN sys.columns b
) Tally
WHERE N <= LEN(@str_Buff);
-- Must just be 1 to 9
WITH cteOther(O) AS
(
SELECT COUNT(*) AS O
FROM @tbl_Temp AS T
WHERE T.MyChar NOT LIKE '[0-9]'
),
-- Or decimal
cteDecimal(D) AS
(
SELECT COUNT(*)
FROM @tbl_Temp AS T
WHERE T.MyChar = '.'
)
SELECT
@int_Return =
CASE WHEN cteDecimal.D = 1 AND cteOther.O = 1 THEN 1
WHEN cteDecimal.D = 0 AND cteOther.O = 0 THEN 1
ELSE 0 END
FROM cteDecimal, cteOther;
-- Return the results
RETURN @int_Return;
END
GO
PRINT 'VALID INTEGERS & DECIMALS'
SELECT dbo.usp_Is_Integer ('121234')
SELECT dbo.usp_Is_Decimal ('123.45')
SELECT dbo.usp_Is_Decimal ('123.')
SELECT dbo.usp_Is_Decimal ('.123')
PRINT 'INVALID INTEGERS & DECIMALS'
SELECT dbo.usp_Is_Integer ('1212#34')
SELECT dbo.usp_Is_Decimal ('123.4.5')
SELECT dbo.usp_Is_Decimal ('!.123')
John Miner
Crafty DBA
www.craftydba.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply