Hello guys,
I would like to identify if the value inside of nvarchar field is numeric or not. I tried this query but didn't help.
SELECT cf.referenceid, ISNULL(cc.shopno,gr.shopNo) AS shopno, ISNULL(cc.safeno,gr.safeNo) AS safeno, ISNULL(cc.cashierno, gr.cashierNo) AS cashierno,
cf.purchaseStatusDate, cc.confirmcanceldatetime, cf.productcode, cf.productdescription, cf.quantity, cf.unitprice, cf.totalprice, ISNULL(cc.status, 0) As status
FROM[gameconfirmresponses] cf LEFT JOIN(SELECT *, Row_number() OVER(partition BY referenceid ORDER BY confirmcanceldatetime) AS row_num FROM[confirmcancels]) AS cc
ON cf.referenceid = cc.referenceid AND cc.row_num = 1 JOIN[GameRequests] AS gr ON gr.referenceId = cf.referenceId WHERE cf.purchasestatusdate >= '2021-08-01 00:00:31.383' AND
cf.purchasestatusdate < '2021-08-20 09:05:31.383' and ISNUMERIC(gr.shopNo) = 1
CREATE TABLE [dbo].[GameRequests](
[Id] [int] IDENTITY(1,1) NOT NULL,
[referenceId] [uniqueidentifier] NULL,
[productCode] [nvarchar](max) NULL,
[quantity] [int] NOT NULL,
[requestDateTime] [datetime] NULL,
[shopNo] [nvarchar](max) NULL,
[safeNo] [nvarchar](max) NULL,
[cashierNo] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.GameRequests] 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] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[GameConfirmResponses](
[Id] [int] IDENTITY(1,1) NOT NULL,
[referenceId] [uniqueidentifier] NOT NULL,
[productCode] [nvarchar](max) NULL,
[quantity] [int] NOT NULL,
[productDescription] [nvarchar](max) NULL,
[totalPrice] [float] NOT NULL,
[purchaseStatusDate] [datetime] NULL,
[requestDateTime] [datetime] NULL,
CONSTRAINT [PK_dbo.GameConfirmResponses] 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] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ConfirmCancels](
[id] [int] IDENTITY(1,1) NOT NULL,
[referenceId] [uniqueidentifier] NOT NULL,
[ConfirmCancelDateTime] [datetime] NULL,
[status] [int] NOT NULL,
[shopNo] [nvarchar](max) NULL,
[safeNo] [nvarchar](max) NULL,
[cashierNo] [nvarchar](max) NULL,
CONSTRAINT [PK_dbo.ConfirmCancels] 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] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO @ GameRequests SELECT ' 4EA2BD81-62CF-4AA2-B7F6-097C1526C4CD ',' 0186479 ', 2021-08-19 09:05:31.383, 'ElaAPP', ' ElaAPP ', ' ElaAPP '
INSERT INTO @ GameConfirmResponses SELECT ' 4EA2BD81-62CF-4AA2-B7F6-097C1526C4CD ',' 0186479 ', 1, ' Product A ', 10 , 2021-08-19 09:05:35.000, 2021-08-19 09:05:33.753
INSERT INTO @ ConfirmCancels SELECT ' 4EA2BD81-62CF-4AA2-B7F6-097C1526C4CD ', 2021-08-19 09:05:33.863, 1, 'ElaAPP', ' ElaAPP ', ' ElaAPP '
INSERT INTO @ GameRequests SELECT ' 4EA2BD81-62CF-4CD2-B1F6-007C1526A4CB',' 0186479 ', 2021-08-18 09:05:31.383, '1902837', ' 03849 ', ' 1 '
INSERT INTO @ GameConfirmResponses SELECT ' 4EA2BD81-62CF-4CD2-B1F6-007C1526A4CB ',' 0186479 ', 1, ' Product A ', 10 , 2021-08-18 09:05:35.000, 2021-08-18 09:05:33.753
INSERT INTO @ ConfirmCancels SELECT ' 4EA2BD81-62CF-4CD2-B1F6-007C1526A4CB', 2021-08-18 09:05:33.863, 1, '1902837', ' 03849', ' 1 '