SQLTeam.com | Weblogs | Forums

NVARCHAR how to identify is numeric?

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 '

What do you mean by "numeric"? Only numeric digits? Decimal point allowed? Comma allowed?

Assuming you mean only digits, then:

WHERE column_name NOT LIKE '%[^0-9]%'

If you want to remove spaces around the value first, then this:

WHERE LTRIM(RTRIM(column_name)) NOT LIKE '%[^0-9]%'