基于字符串中的字符从查询中删除行项目sql
问题描述:
我有一个sql查询,我需要删除特定的行,基于查询中生成的varchar中的倒数第三个数字。该查询以格式“yyyy-MM-dd hh:mm:ss”采用小日期时间并将其转换为varchar。例如:日期“2015-05-04 06:03:00”将成为varchar 05040603.我需要删除第三个到最后一个varchar数字是9,1,4或0的任何行项目。以下是我的当前代码:基于字符串中的字符从查询中删除行项目sql
SELECT DISTINCT
RIGHT('0' + CAST(DATEPART(M, TableA.DateTime) AS varchar), 2)
+ RIGHT ('0' + Cast(DATEPART(DD, TableA.DateTime) AS varchar), 2)
+ RIGHT('0' + CAST(DATEPART(HH, TableA.DateTime) AS varchar), 2)
+ RIGHT('0' + CAST(DATEPART(MINUTE, TableA.DateTime) AS varchar), 2) AS 'VarChar Number',
ColA, DateTime
FROM
TableA
WHERE
ColB IS NOT NULL
AND DateTime NOT IN (SELECT DateTime From TableB)
AND DateTime NOT IN (SELECT DateTime From tableC
WHERE DateTime IS NOT NULL)
在这个例子中的表A具有我的小日期时间和正在从它产生的varchar
数。 ColA来自表A并且仅仅是信息。 Where语句只是将日期时间与其他表进行比较,以便根据日期是否已经出现在这些表中来将其删除。
答
SELECT DISTINCT
RIGHT('0' + CAST(DATEPART(M, TableA.DateTime) AS varchar), 2) +
RIGHT ('0' + Cast(DATEPART(DD, TableA.DateTime) AS varchar), 2) +
RIGHT('0' + CAST(DATEPART(HH, TableA.DateTime) AS varchar), 2) +
RIGHT('0' + CAST(DATEPART(MINUTE, TableA.DateTime) AS varchar), 2) AS 'VarChar Number',
ColA,
DateTime
FROM
TableA
WHERE
ColB IS NOT NULL AND
DateTime NOT IN (SELECT DateTime From TableB) AND
DateTime NOT IN (SELECT DateTime From tableC WHERE DateTime IS NOT NULL) AND
RIGHT(CAST(DATEPART(HH, TableA.DateTime) AS varchar), 1) NOT IN ('0', '1', '4', '9')
+0
这工作,谢谢。 – Cheddar
答
您可以使用substring
来执行此操作。
select * from
(
SELECT DISTINCT
RIGHT('0' + CAST(DATEPART(M, TableA.DateTime) AS varchar), 2)
+ RIGHT ('0' + Cast(DATEPART(DD, TableA.DateTime) AS varchar), 2)
+ RIGHT('0' + CAST(DATEPART(HH, TableA.DateTime) AS varchar), 2)
+ RIGHT('0' + CAST(DATEPART(MINUTE, TableA.DateTime) AS varchar), 2) AS [VarChar Number],
ColA, DateTime FROM TableA WHERE ColB IS NOT NULL
AND DateTime NOT IN (SELECT DateTime From TableB)
AND DateTime NOT IN (SELECT DateTime From tableC WHERE DateTime IS NOT NULL)
) t
where substring([VarChar Number], 6, 1) not in ('0','1','4','9')
答
...
WHERE
ColB IS NOT NULL
AND DateTime NOT IN (SELECT DateTime From TableB)
AND DateTime NOT IN (SELECT DateTime From tableC
WHERE DateTime IS NOT NULL)
and datepart(minute, TableA.DateTime) % 10 not in (0, 1, 4, 9)
我不认为有任何理由与字符串值勾搭,只是提取分钟,并与工作。理解意图并避免问题更为清楚,就像vkp的答案一样,意外地看着第6个位置而不是第8个位置。
您正在使用哪种类型的SQL?微软,Oracle,PostgreSQL,MySQL? –
Microsoft,特别是SQL Server Management Studio。但是,我将把代码放入vb.net程序并使用它填充datagridview。 – Cheddar