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
Sql: How to to Sum Two Values from Different Tables
Is There Something Equivalent to Argmax in SQL
Turn Off SQL Logging While Keeping Settings.Debug
How to Update All Columns with Insert ... on Conflict ...
How Does Subquery in Select Statement Work in Oracle
How to Search All Text Fields in a Db for Some Substring with T-Sql
Mysql: Which to Use When: Drop Table, Truncate Table, Delete from Table
One-To-Many Query Selecting All Parents and Single Top Child for Each Parent
Oracle: How to Get Percent of Total by a Query
Group Data by the Change of Grouping Column Value in Order
Table in Excel from SQL Server Stored Procedure with Parameter Field in Workbook
Do Indexes Work with "In" Clause
How to Escape Non-Format Characters in Oracle's To_Char
How to Manually Execute SQL Commands in Ruby on Rails Using Nuodb