使用 SQL Server 2012 提取字符串

发布于 2025-01-09 13:15:35 字数 150 浏览 0 评论 0原文

我有一个形式为“

<div>#FIRST#12345#</div>

如何在 SQL Server 2012 中使用 T-SQL 从此字符串中提取数字部分？” 形式的字符串。请注意该数字的长度是可变的

原文

I have a string in the form of

<div>#FIRST#12345#</div>

How do I extract the number part from this string using T-SQL in SQL Server 2012? Note the number has variable length

分享到QQ

分享到微博

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

发布评论

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

忘你却要生生世世 2025-01-16 13:15:36

仅使用 t-sql 字符串函数，您可以尝试：

create table t(col varchar(50))
insert into t select '<div>#FIRST#12345#</div>'
insert into t select '<div>#THIRD#543#</div>'
insert into t select '<div>#SECOND#3690123#</div>'

select col, 
  case when p1.v=0 or p2.v <= p1.v then '' 
    else Substring(col, p1.v, p2.v-p1.v) 
  end ExtractedNumber
from t
cross apply(values(CharIndex('#',col,7) + 1))p1(v)
cross apply(values(CharIndex('#',col, p1.v + 1)))p2(v)

输出：

注意，这不会处理任何“边缘”情况，并假设数据如所描述的那样。

Using just t-sql string functions you can try:

create table t(col varchar(50))
insert into t select '<div>#FIRST#12345#</div>'
insert into t select '<div>#THIRD#543#</div>'
insert into t select '<div>#SECOND#3690123#</div>'

select col, 
  case when p1.v=0 or p2.v <= p1.v then '' 
    else Substring(col, p1.v, p2.v-p1.v) 
  end ExtractedNumber
from t
cross apply(values(CharIndex('#',col,7) + 1))p1(v)
cross apply(values(CharIndex('#',col, p1.v + 1)))p2(v)

Output:

Caveat, this doesn't handle any "edge" cases and assumes data is as described.

回复收藏 0 原文

番薯 2025-01-16 13:15:36

由于缺少最小可重现示例，所以草率行事。

假设它是XML数据类型列。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
('<div>#FIRST#12345#</div>'),
('<div>#FIRST#770770#</div>');
-- DDL and sample data population, end

SELECT t.*
    , LEFT(x, CHARINDEX('#', x) - 1) AS Result
FROM @tbl t
    CROSS APPLY xmldata.nodes('/div/text()') AS t1(c)
    CROSS APPLY (SELECT REPLACE(c.value('.', 'VARCHAR(100)'), '#FIRST#' ,'')) AS t2(x);

输出

+----+---------------------------+--------+
| ID |          xmldata          | Result |
+----+---------------------------+--------+
|  1 | <div>#FIRST#12345#</div>  |  12345 |
|  2 | <div>#FIRST#770770#</div> | 770770 |
+----+---------------------------+--------+

Shooting from the hip due to a missing minimal reproducible example.

Assuming that it is XML data type column.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
('<div>#FIRST#12345#</div>'),
('<div>#FIRST#770770#</div>');
-- DDL and sample data population, end

SELECT t.*
    , LEFT(x, CHARINDEX('#', x) - 1) AS Result
FROM @tbl t
    CROSS APPLY xmldata.nodes('/div/text()') AS t1(c)
    CROSS APPLY (SELECT REPLACE(c.value('.', 'VARCHAR(100)'), '#FIRST#' ,'')) AS t2(x);

Output

+----+---------------------------+--------+
| ID |          xmldata          | Result |
+----+---------------------------+--------+
|  1 | <div>#FIRST#12345#</div>  |  12345 |
|  2 | <div>#FIRST#770770#</div> | 770770 |
+----+---------------------------+--------+

回复收藏 0 原文

~没有更多了~