May 17, 2013 at 6:50 am
HI,
I have a column which is defined as Decimal(23,10).
I want to get the values from the column which are not decimals.
Kindly provide a solution ,
May 17, 2013 at 6:56 am
your not real clear on the question;
if the column is typed as (23,10), then every non-null value is a decimal, by definition;
so do you mean which values are null?
do you mean which are exactly equal to their integer conversions?
Lowell
May 17, 2013 at 6:58 am
Not sure what you mean here. All of the numbers are decimals.
Are you looking for the values that are a whole number?
1.0, but not 1.1???
This may work
SELECT *
FROM Numbers
WHERE Number1 - CONVERT(int, Number1) = 0
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 17, 2013 at 6:59 am
select * from YourTable
where FLOOR(YourDecimalColumn) = YourDecimalColumn --can be converted to an int without data loss
AND YourDecimalColumn < 2147483647 --max size of an int
Lowell
May 20, 2013 at 1:16 am
Hi ..
I have a Column 'X' NVARCHAR(10) in Table 'X' which is mapped to Column 'Y' Decimal(23,10) of Table 'Y'
I want to check in Table 'y' which are not of type Decimal.
May 20, 2013 at 2:26 am
By "not decimal" I guess you mean like "10.0" (10), 8.0 (8) right?
If so the above posters answer should work:
SELECT * FROM y
WHERE column_y - CONVERT(int, column_y) = 0
Dird
May 20, 2013 at 5:07 am
greeshatu (5/20/2013)
Hi ..I have a Column 'X' NVARCHAR(10) in Table 'X' which is mapped to Column 'Y' Decimal(23,10) of Table 'Y'
I want to check in Table 'y' which are not of type Decimal.
In this specific scenario,You can check which values in your "Table X" can be converted to decimal.
ALL items in "Table Y" are already decimal, because of the column definition.
either of these two functions can help evaluate whether a varchar/char/nvarchar/nchar value can be converted or not.
CREATE FUNCTION IsNumeric2(@str varchar(20))
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
declare @results int
SELECT @results = CASE
WHEN (PATINDEX('%[0-9,.]%', @STR) = 0) AND (LEN(@str) - LEN(REPLACE(@str,'.','')) <= 1)
THEN 1
ELSE 0
END
return @results
END --FUNCTION
GO
CREATE FUNCTION IsNumeric3(@str varchar(20))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN(SELECT CASE
WHEN (PATINDEX('%[0-9,.]%', @STR) = 0) AND (LEN(@str) - LEN(REPLACE(@str,'.','')) <= 1)
THEN 1
ELSE 0
END As boolNumeric
) --END FUNCTION
Lowell
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply