选择查询以删除非数字字符 [英] Select query to remove non-numeric characters

问题描述

我在具有可变 alpha 长度的列中有脏数据.我只想去掉任何不是 0-9 的东西.

I've got dirty data in a column with variable alpha length. I just want to strip out anything that is not 0-9.

我不想运行函数或过程.我有一个类似的脚本，它只是在文本之后抓取数值，它看起来像这样:

I do not want to run a function or proc. I have a script that is similar that just grabs the numeric value after text, it looks like this:

Update TableName
set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int)
where Columnofdirtydata like 'Alpha #%'
And ColumntoUpdate is Null

我认为它会工作得很好，直到我发现一些我认为只会采用 Alpha # 12345789 格式的数据字段不是.

I thought it would work pretty good until I found that some of the data fields I thought would just be in the format Alpha # 12345789 are not.

需要剥离的数据示例

AB ABCDE # 123
ABCDE# 123
AB: ABC# 123

我只想要 123.确实，所有数据字段在数字之前都有#.

I just want the 123. It is true that all data fields do have the # prior to the number.

我尝试了 substring 和 PatIndex，但我不太明白语法正确或其他什么.有人对解决这个问题的最佳方法有什么建议吗?

I tried substring and PatIndex, but I'm not quite getting the syntax correct or something. Anyone have any advice on the best way to address this?

推荐答案

见这个博客文章 关于在 SQL Server 中从字符串中提取数字.以下是在您的示例中使用字符串的示例:

See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example:

DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'

SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)

这篇关于选择查询以删除非数字字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！