Executing a CASE

Question

SQL

There is a column in which there is an arbitrary number of lines in which there can be either numbers or lines, and you need to select only those for which this column is filled with a number and return them to int. We write a query like this:

SELECT
Id
,stringwithnumeric
WHERE
CASE
WHEN ISNUMERIC (stringwithnumeric)=1
THEN CAST (stringwithnumeric AS INT)
ELSE 0
END > 0

The error falls out that we cannot varchar in intcast. Well, if we don’t have when for the case, then, according to normal human logic, it will not be executed, I thought so. It turns out that case goes through the execution of all branches. How does this work more efficiently than discarding by condition, and then applying then? How can you explain such a strange execution logic?

Answer 1

Or maybe where there is "0.1" or even just "."? It is numeric, but not int.

Answer 2

Here is a table with test data
CREATE TABLE [dbo].[TableTest](
[Id] [int] IDENTITY(1,1) NOT NULL,
[stringwithnumeric] [varchar](50) NULL,
CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED
(
[ Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[TableTest] ON
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (1, N'1')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (2, N'dsa ')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (3, N'3')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (4, N'3')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (5, N'gg ')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (6, N'del')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (7, N '33')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (8, N'67')
INSERT [dbo].[TableTest] ([Id], [stringwithnumeric]) VALUES (9 , N'97')
SET IDENTITY_INSERT [dbo].[TableTest] OFF
Here is the query
SELECT
Id
,stringwithnumeric
FROM dbo.TableTest
WHERE ISNUMERIC (stringwithnumeric)=1
Or do you want something else?

Answer 3

according to the GalinaM plate, your request is perfectly executed.
But if you insert a string like '1.1' into the data, then your request breaks,
but this condition works:

WHERE 
  CASE 
    --WHEN ISNUMERIC (stringwithnumeric)=1 THEN CAST (stringwithnumeric AS INT) 
    WHEN ISNUMERIC (stringwithnumeric)=1 THEN convert(int, convert(numeric(19, 3), stringwithnumeric))
    ELSE 0 
  END > 0

ZY wean to use CAST - it is left for backward compatibility. Use CONVERT

Answer 4

so, check the data in the table,
I'm 80% sure that it has strings of the form "[number].[number]" or close to such a
shorter one, for which IsNumeric() will return 1, but type casting will not work by the
way, my version breaks if the data contains the value '.'
Threat casts are bad because
a) converting a date from a string or vice versa is unreliable
b) it is still outdated. This means that sooner or later it will not be in the cheekbone (and is it in MS SQL 2012?).
And since some successful pieces of code tend to migrate from project to project, it may turn out that it will pop up somewhere, even if you do not update the DBMS of this project.