如何从属于不同数据类型的列中删除记录?

问题描述

所以我手动在表中插入了几条记录(不使用存储过程)，并且COLMN"列下的行很好.然后我尝试通过在存储过程中使用动态 SQL 来自动化它.不幸的是，我最终附加了垃圾记录(在本来是 Char 的列中输入了 float ).如何删除这些不属于的选定垃圾记录?这是我的桌子的样子:

So I inserted few records in a table manually (without using Stored procedure) and rows were fine under a column "COLMN". Then I tried to automate it by using dynamic-SQL in stored procedure. Unfortunately I ended up appending rubbish records (entered float in a column which was meant to be Char). How can I delete these selected rubbish records which don't belong?Here is how my table looks like:

...而且我想删除那些数字记录(从列COLMN")我计划通过以下方式实现:改变表格并添加一个序列列，更新序列列，对其进行排序并相应地删除顶部或底部 N 行并最后删除该列.像这样:

...And I want to delete those numeric records (from column "COLMN") I planned to achieve this by :Altering the table and adding a column for sequence, updating the sequence column, sorting it and accordingly deleting the top or bottom N rows and lastly dropping the column. Something like this:

ALTER TABLE PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE_5 ADD SEQ_NUM INT;

UPDATE  PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE_5
SET SEQ_NUM = ROW_NUMBER() OVER(ORDER BY COLMN)

但我们不能使用这样的分析函数.

But we can't use analytical functions like that.

让我知道是否有简单的方法来做到这一点.

Let me know if there is an easy way to do it.

谢谢

皮尤什

推荐答案

查找数字字符串的最简单方法可能是比较列名的大小写，如果是数字则两者相同:

The easiest way to find numeric strings is probably comparing upper and lower case of the column name, if it's numeric both will be the same:

SELECT * FROM PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE_5
WHERE LOWER(COLMN) = UPPER(COLMN) (CASESPECIFIC)
   OR COLMN IS NULL

如果返回正确的行，只需更改为 DELETE.

If this returns the correct rows simply change to a DELETE.

这篇关于如何从属于不同数据类型的列中删除记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！