如何使用 LRTIM 和 RTRIM 查找无效记录并将其插入到错误表中？

发布于 2024-10-14 09:47:42 字数 947 浏览 0 评论 0原文

我有一个查询，可以从表中的每一列中查找无效记录。但所有列的数据类型长度并不相同。我这样说是因为当我使用 LTRIM 和 RTRIM 时，它会给出错误“列“SubscriberLastName”的类型与 UNPIVOT 列表中指定的其他列的类型冲突”并且如果我 CAST 该列然后没有结果。

有人可以帮我吗？

Insert into ErrorTable (------) select (-----)
From (
            select [SubscriberDataId]
            ,Case When ltrim(rtrim([SubscriberCode])) = '' Then [SubscriberCode] Else 'CorrectValue' end as [SubscriberCode]
            ,Case When ltrim(rtrim([SubscriberLastName]))= '' Then [SubscriberLastName] Else 'CorrectValue' end as [SubscriberLastName]
            ,Case When ltrim(rtrim([SubscriberFirstName]))= ''  Then [SubscriberFirstName] Else 'CorrectValue' end as [SubscriberFirstName]
            from Facets.SubscriberData) [sd]
            Unpivot
            (ErrorValue for FieldName in ([SubscriberCode],
            [SubscriberLastName],[SubscriberFirstName] )) as x
            where x.ErrorValue <> 'CorrectValue'

原文

I have a query that finds the invalid records from each column in a table. but the length of the data type is not same for all the columns. I am saying this because when I use LTRIM and RTRIM , it gives an error "The type of column "SubscriberLastName" conflicts with the type of other columns specified in the UNPIVOT list" and if I CAST that column then no result.

Can anybody help me with that?

Insert into ErrorTable (------) select (-----)
From (
            select [SubscriberDataId]
            ,Case When ltrim(rtrim([SubscriberCode])) = '' Then [SubscriberCode] Else 'CorrectValue' end as [SubscriberCode]
            ,Case When ltrim(rtrim([SubscriberLastName]))= '' Then [SubscriberLastName] Else 'CorrectValue' end as [SubscriberLastName]
            ,Case When ltrim(rtrim([SubscriberFirstName]))= ''  Then [SubscriberFirstName] Else 'CorrectValue' end as [SubscriberFirstName]
            from Facets.SubscriberData) [sd]
            Unpivot
            (ErrorValue for FieldName in ([SubscriberCode],
            [SubscriberLastName],[SubscriberFirstName] )) as x
            where x.ErrorValue <> 'CorrectValue'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝颜夕 2024-10-21 09:47:42

您需要确保类型相同，而不仅仅是长度相同。

Insert into ErrorTable (------) select (-----)
From (
            select [SubscriberDataId]
            ,Case When ltrim(rtrim([SubscriberCode])) = '' Then CAST([SubscriberCode] AS NVARCHAR(MAX)) Else 'CorrectValue' end as [SubscriberCode]
            ,Case When ltrim(rtrim([SubscriberLastName]))= '' Then CAST([SubscriberLastName] AS NVARCHAR(MAX)) Else 'CorrectValue' end as [SubscriberLastName]
            ,Case When ltrim(rtrim([SubscriberFirstName]))= ''  Then CAST([SubscriberFirstName] AS NVARCHAR(MAX)) Else 'CorrectValue' end as [SubscriberFirstName]
            from Facets.SubscriberData) [sd]
            Unpivot
            (ErrorValue for FieldName in ([SubscriberCode],
            [SubscriberLastName],[SubscriberFirstName] )) as x
            where x.ErrorValue <> 'CorrectValue'

You need to make sure the TYPEs are the same, not just the lengths.

Insert into ErrorTable (------) select (-----)
From (
            select [SubscriberDataId]
            ,Case When ltrim(rtrim([SubscriberCode])) = '' Then CAST([SubscriberCode] AS NVARCHAR(MAX)) Else 'CorrectValue' end as [SubscriberCode]
            ,Case When ltrim(rtrim([SubscriberLastName]))= '' Then CAST([SubscriberLastName] AS NVARCHAR(MAX)) Else 'CorrectValue' end as [SubscriberLastName]
            ,Case When ltrim(rtrim([SubscriberFirstName]))= ''  Then CAST([SubscriberFirstName] AS NVARCHAR(MAX)) Else 'CorrectValue' end as [SubscriberFirstName]
            from Facets.SubscriberData) [sd]
            Unpivot
            (ErrorValue for FieldName in ([SubscriberCode],
            [SubscriberLastName],[SubscriberFirstName] )) as x
            where x.ErrorValue <> 'CorrectValue'

回复收藏 0 原文

~没有更多了~