问题描述
我有一张桌子
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TestAB]( [A] [int] IDENTITY(1,1) NOT NULL, [B] [nvarchar](10) NULL, CONSTRAINT [PK_TestAB] PRIMARY KEY CLUSTERED ( [A] 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
值喜欢
INSERT INTO [TestAB]([B]) VALUES('A1'); INSERT INTO [TestAB]([B]) VALUES('A2'); INSERT INTO [TestAB]([B]) VALUES('A5'); INSERT INTO [TestAB]([B]) VALUES('A4'); INSERT INTO [TestAB]([B]) VALUES('AA5'); INSERT INTO [TestAB]([B]) VALUES('A9'); INSERT INTO [TestAB]([B]) VALUES('C7'); INSERT INTO [TestAB]([B]) VALUES('D8'); INSERT INTO [TestAB]([B]) VALUES('D-9'); INSERT INTO [TestAB]([B]) VALUES('R$10');
我想去掉非数字字符并将 nvarchar 数字转换为 int,然后比较它们的位置!=
I want to strip off the non numeric characters and cast the nvarchar numbers to int and then compare where they are !=
推荐答案
Select A,B,Case when A<>B then 0 else 1 end from ( select A, Cast( Left(SubString(B, PatIndex('%[0-9.-]%', B), 100), PatIndex('%[^0-9.-]%', SubString(B, PatIndex('%[0-9.-]%', B), 100)+'_' )-1) as int) as B from TestAB ) aa where A<>B
SQL-Fiddle