SQL Server - Check to See If Cast Is Possible

sql server - check to see if cast is possible

Well, in SQL Server 2012 you could use the new TRY_CAST(), but with SQL Server 2008, you should be able to use ISNUMERIC(), and then include handling for values that do not pass that test.

SQL Server 2014: How to cast a string value to int only if the value can be casted?

The simple solution would be to use COALSECE:

DECLARE @ErrorCode nvarchar(1024)
SET @ErrorCode = 'a10'
SELECT COALSECE(CAST(TRY_CAST(@ErrorCode AS int) as nvarchar(1024)), @ErrorCode)

However, I don't see the point of casting to int and then back to nvarchar.

TSQL - Cast string to integer or return default value

If you are on SQL Server 2012 (or newer):

Use the TRY_CONVERT function.

If you are on SQL Server 2005, 2008, or 2008 R2:

Create a user defined function. This will avoid the issues that Fedor Hajdu mentioned with regards to currency, fractional numbers, etc:

CREATE FUNCTION dbo.TryConvertInt(@Value varchar(18))
RETURNS int
AS
BEGIN
SET @Value = REPLACE(@Value, ',', '')
IF ISNUMERIC(@Value + 'e0') = 0 RETURN NULL
IF ( CHARINDEX('.', @Value) > 0 AND CONVERT(bigint, PARSENAME(@Value, 1)) <> 0 ) RETURN NULL
DECLARE @I bigint =
CASE
WHEN CHARINDEX('.', @Value) > 0 THEN CONVERT(bigint, PARSENAME(@Value, 2))
ELSE CONVERT(bigint, @Value)
END
IF ABS(@I) > 2147483647 RETURN NULL
RETURN @I
END
GO

-- Testing
DECLARE @Test TABLE(Value nvarchar(50)) -- Result
INSERT INTO @Test SELECT '1234' -- 1234
INSERT INTO @Test SELECT '1,234' -- 1234
INSERT INTO @Test SELECT '1234.0' -- 1234
INSERT INTO @Test SELECT '-1234' -- -1234
INSERT INTO @Test SELECT '$1234' -- NULL
INSERT INTO @Test SELECT '1234e10' -- NULL
INSERT INTO @Test SELECT '1234 5678' -- NULL
INSERT INTO @Test SELECT '123-456' -- NULL
INSERT INTO @Test SELECT '1234.5' -- NULL
INSERT INTO @Test SELECT '123456789000000' -- NULL
INSERT INTO @Test SELECT 'N/A' -- NULL
SELECT Value, dbo.TryConvertInt(Value) FROM @Test

Reference: I used this page extensively when creating my solution.

CAST and IsNumeric

IsNumeric returns 1 if the varchar value can be converted to ANY number type. This includes int, bigint, decimal, numeric, real & float.

Scientific notation could be causing you a problem. For example:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values(NULL)
Insert Into @Temp Values('1')
Insert Into @Temp Values('1e4')
Insert Into @Temp Values('Not a number')

Select Cast(Data as bigint)
From @Temp
Where IsNumeric(Data) = 1 And Data Is Not NULL

There is a trick you can use with IsNumeric so that it returns 0 for numbers with scientific notation. You can apply a similar trick to prevent decimal values.

IsNumeric(YourColumn + 'e0')

IsNumeric(YourColumn + '.0e0')

Try it out.

SELECT CAST(myVarcharColumn AS bigint)
FROM myTable
WHERE IsNumeric(myVarcharColumn + '.0e0') = 1 AND myVarcharColumn IS NOT NULL
GROUP BY myVarcharColumn

Understanding why my CAST to INT is not working

As mentioned in the comments, you can't really control whether the CAST is attempted before the filter; it all depends on how SQL Server chooses to optimize the query.

Also, checking for the existence of a decimal and no a-z is not a very reliable way to filter out non-numerics.

Both of the following methods eliminate the need, I think, for the CTE, because you were only using it to try to force the filter to happen first. CTEs can be folded into the rest of the query; perhaps worth a read are some other tidbits about CTEs.

Try TRY_CONVERT() (which I wrote about here):

WHERE TRY_CONVERT(int, CTE.OrderNo) >= 21187;

If you are using an older version that doesn't support TRY_CONVERT, you can try CASE (perhaps another useful read):

WHERE CASE WHEN ISNUMERIC(CTE.OrderNo) = 1 THEN CTE.OrderNo END >= 21187;

But ISNUMERIC() is not all that reliable either.

Since now we know the version, your query can be simplified to:

SELECT DISTINCT OrderNo
FROM dbo.OrderDet
WHERE PartNo LIKE '%.%'
AND TRY_CONVERT(int, OrderNo) >= 21187;

SQL Case and Cast in Count function

as I commented before, ms.Mailing_Id is an int and ag.mailingid is a varchar. a colleague helped me out with this:

FROM [StrongMailTracking].[dbo].[SM_MAILING_SUMMARY] ms left join sm_aggregate_log ag on CAST(ms.mailing_id As varchar(255)) = ag.mailingid



Related Topics



Leave a reply



Submit