当前位置：文江博客话题详情

SQL 剥离文本并转换为整数

发布于 2024-07-05 11:48:39 字数 211 浏览 8 评论 0原文

在我的数据库（SQL 2005）中，我有一个包含注释的字段，但在注释中我有一个 id，我想只删除 id，如果可能的话，将其转换为 int：

activation success of id 1010101

上面的行是 db 字段中数据的确切结构。

不，我不想在应用程序的代码中执行此操作，实际上我不想碰它，以防万一您想知道;-)

原文

分享到QQ

分享到微博

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

发布评论

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

静待花开 2024-07-12 11:48:39

如果注释字符串与该字符串完全相同，则可以使用替换。

select   replace(comment_col, 'activation successful of id ', '') as id from ....

但几乎可以肯定不会 - 激活不成功怎么办？
你可能最终会得到嵌套的替换语句

select   replace(replace(comment_col, 'activation not successful of id ', ''), 'activation successful of id ', '') as id from ....

[抱歉，无法从这个编辑屏幕判断这是否是完全有效的sql]

这开始变得混乱；您可以考虑创建一个函数并将替换语句放入其中。

如果这是一份一次性工作，那其实并不重要。您也可以使用正则表达式，但这非常慢（无论如何意味着您现在有两个问题）。

If the comment string is EXACTLY like that you can use replace.

select   replace(comment_col, 'activation successful of id ', '') as id from ....

It almost certainly won't be though - what about unsuccessful Activations?
You might end up with nested replace statements

select   replace(replace(comment_col, 'activation not successful of id ', ''), 'activation successful of id ', '') as id from ....

[sorry can't tell from this edit screen if that's entirely valid sql]

That starts to get messy; you might consider creating a function and putting the replace statements in that.

If this is a one off job, it won't really matter. You could also use a regex, but that's quite slow (and in any case mean you now have 2 problems).

回复收藏 0 原文

故事↓在人 2024-07-12 11:48:39

CAST(REVERSE(LEFT(REVERSE(@Test),CHARINDEX(' ',REVERSE(@Test))-1)) AS INTEGER)

CAST(REVERSE(LEFT(REVERSE(@Test),CHARINDEX(' ',REVERSE(@Test))-1)) AS INTEGER)

回复收藏 0 原文

梦屿孤独相伴 2024-07-12 11:48:39

-- Test table, you will probably use some query  
DECLARE @testTable TABLE(comment VARCHAR(255))  
INSERT INTO @testTable(comment)  
    VALUES ('activation successful of id 1010101')

-- Use Charindex to find "id " then isolate the numeric part  
-- Finally check to make sure the number is numeric before converting  
SELECT CASE WHEN ISNUMERIC(JUSTNUMBER)=1 THEN CAST(JUSTNUMBER AS INTEGER) ELSE -1 END  
FROM (  
       select right(comment, len(comment) - charindex('id ', comment)-2) as justnumber  
       from @testtable) TT

我还要补充一点，这种方法更多地基于集合，因此对于一堆数据值来说更有效。但仅将一个值作为变量来做到这一点是非常容易的。您可以使用像 @chvComment 这样的变量，而不是使用列注释。

-- Test table, you will probably use some query  
DECLARE @testTable TABLE(comment VARCHAR(255))  
INSERT INTO @testTable(comment)  
    VALUES ('activation successful of id 1010101')

-- Use Charindex to find "id " then isolate the numeric part  
-- Finally check to make sure the number is numeric before converting  
SELECT CASE WHEN ISNUMERIC(JUSTNUMBER)=1 THEN CAST(JUSTNUMBER AS INTEGER) ELSE -1 END  
FROM (  
       select right(comment, len(comment) - charindex('id ', comment)-2) as justnumber  
       from @testtable) TT

I would also add that this approach is more set based and hence more efficient for a bunch of data values. But it is super easy to do it just for one value as a variable. Instead of using the column comment you can use a variable like @chvComment.

回复收藏 0 原文

我不咬妳我踢妳 2024-07-12 11:48:39

select cast(right(column_name,charindex(' ',reverse(column_name))) as int)

select cast(right(column_name,charindex(' ',reverse(column_name))) as int)

回复收藏 0 原文

无悔心 2024-07-12 11:48:39

这应该可以解决问题：

SELECT SUBSTRING(column, PATINDEX('%[0-9]%', column), 999)
FROM table

根据您的示例数据，字符串中仅出现一次整数并且它位于末尾。

This should do the trick:

SELECT SUBSTRING(column, PATINDEX('%[0-9]%', column), 999)
FROM table

Based on your sample data, this that there is only one occurence of an integer in the string and that it is at the end.

回复收藏 0 原文

烂人 2024-07-12 11:48:39

我目前没有办法测试它，但是：

select convert(int, substring(fieldName, len('activation successful of id '), len(fieldName) - len('activation successful of id '))) from tableName

I don't have a means to test it at the moment, but:

select convert(int, substring(fieldName, len('activation successful of id '), len(fieldName) - len('activation successful of id '))) from tableName

回复收藏 0 原文

最舍不得你 2024-07-12 11:48:39

您愿意编写一些代码吗？一种选择是创建 CLR 用户定义函数，然后使用正则表达式。您可以在此处找到更多详细信息。这将处理复杂的字符串。

如果您上面的行始终格式为“id ####### 激活成功”，并且您的号码位于字段末尾，那么：

declare @myColumn varchar(100)
set @myColumn = 'activation successful of id 1010102'


SELECT
    @myColumn as [OriginalColumn]
,   CONVERT(int, REVERSE(LEFT(REVERSE(@myColumn), CHARINDEX(' ', REVERSE(@myColumn))))) as [DesiredColumn]

将为您提供：

OriginalColumn                           DesiredColumn
---------------------------------------- -------------
activation successful of id 1010102      1010102

(1 row(s) affected)

Would you be open to writing a bit of code? One option, create a CLR User Defined function, then use Regex. You can find more details here. This will handle complex strings.

If your above line is always formatted as 'activation successful of id #######', with your number at the end of the field, then:

declare @myColumn varchar(100)
set @myColumn = 'activation successful of id 1010102'


SELECT
    @myColumn as [OriginalColumn]
,   CONVERT(int, REVERSE(LEFT(REVERSE(@myColumn), CHARINDEX(' ', REVERSE(@myColumn))))) as [DesiredColumn]

Will give you:

OriginalColumn                           DesiredColumn
---------------------------------------- -------------
activation successful of id 1010102      1010102

(1 row(s) affected)

回复收藏 0 原文

~没有更多了~