blocks|key|1679093|text|由于您没有提供DB供应商，这里有两篇文章分别针对SQL+Server和Oracle回答这个问题……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1679094|T-SQL:+Opposite+to+string+concatenation+-+how+to+split+string+into+multiple+records|offset|length|1679095|Splitting+comma+separated+string+in+a+PL/SQL+stored+proc|1679096|如果你正在使用其他的DBMS，去搜索“拆分文本”。我几乎可以保证您不是第一个问这个问题的人，而且每个DBMS版本都有答案。|1679097|正如您所说的，格式是恒定的，您也可以使用SUBSTRING函数来做一些更简单的事情。|1679098|编辑以响应操作注释...|1679099|由于您使用的是SQL+Server，并且您说这些值始终采用一致的格式，因此您可以做一些简单的事情，如使用SUBSTRING获取值的每个部分并将其赋值给T-SQL变量，然后在T-SQL变量中使用它们来执行任何您想要的操作，比如在查询的谓词中使用它们。|1679100|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco|1|https://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc^0|0|0|2B|0|0|0|1K|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@]|9|@$D|11|E|12|1|13]]|A|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@$D|15|E|16|1|17]]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1A|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1B|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|Q|$R|$5|S|T|U|A|$V|W]]|X|$5|S|T|U|A|$V|Y]]]]

Since you didn't provide the DB vendor, here's two posts that answer this question for SQL Server and Oracle respectively...

<a href="https://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco">T-SQL: Opposite to string concatenation - how to split string into multiple records</a>

<a href="https://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc">Splitting comma separated string in a PL/SQL stored proc</a>

And if you're using some other DBMS, go search for "splitting text ". I can almost guarantee you're not the first one to ask, and there's answers for every DBMS flavor out there.

As you said the format is constant though, you could also do something simpler using a SUBSTRING function.

EDIT in response to OP comment...

Since you're using SQL Server, and you said that these values are always in a consistent format, you can do something as simple as using SUBSTRING to get each part of the value and assign them to T-SQL variables, where you can then use them to do whatever you want, like using them in the predicate of a query.

blocks|key|3403896|text|如果集合的增量不超过4，则可以使用Parsename检索结果|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3403897|+Declare+@Num+varchar(20)
+Set+@Num='1-117+3-134+3-133'

+select+parsename(replace+(@Num,'+','.'),3)

+Result+:-+1-117

+Now+again+use+parsename+on+the+same+resultset+

+Select+parsename(replace(parsename(replace+(@Num,'+','.'),3),'-','.'),1)

+Result+:-+117|code-block|syntax|javascript|3403898|如果有4个以上的值，则使用拆分函数|3403899|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Incase if the sets does not increase by more than 4 then you can use Parsename to retrieve the result

<pre><code> Declare @Num varchar(20)
 Set @Num='1-117 3-134 3-133'

 select parsename(replace (@Num,' ','.'),3)

 Result :- 1-117

 Now again use parsename on the same resultset 

 Select parsename(replace(parsename(replace (@Num,' ','.'),3),'-','.'),1)

 Result :- 117
</code></pre>

If the there are more than 4 values then use split functions

blocks|key|3316324|text|假设您所说的格式始终是#-#(恰好是1位数字、一个破折号和3位数字)是正确的，这是相当简单的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3316325|WITH+EquipmentSettings+AS+(
+++SELECT
++++++S.*,
++++++Convert(int,+Substring(S.AwfulMultivalue,+V.Value+*+6+-+5,+1)+EquipmentID,
++++++Convert(int,+Substring(S.AwfulMultivalue,+V.Value+*+6+-+3,+3)+Settings
+++FROM
++++++SourceTable+S
++++++INNER+JOIN+master.dbo.spt_values+V
+++++++++ON+V.Value+BETWEEN+1+AND+Len(S.AwfulMultivalue)+/+6
+++WHERE
++++++V.type+=+'P'
)
SELECT
+++E.Whatever,
+++D.Whatever
FROM
+++EquipmentSettings+E
+++INNER+JOIN+DestinationTable+D
++++++ON+E.EquipmentID+=+D.EquipmentID
++++++AND+E.Settings+=+D.Settings|code-block|syntax|javascript|3316326|在SQL+Server+2005%2B中，此查询将支持字符串中的1365个值。|3316327|如果数字的长度可以改变，那就有点难了。让我知道。|3316328|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Assuming that what you said is true about the format always being #-### (exactly 1 digit, a dash, and 3 digits) this is fairly easy.

<pre><code>WITH EquipmentSettings AS (
 SELECT
 S.*,
 Convert(int, Substring(S.AwfulMultivalue, V.Value * 6 - 5, 1) EquipmentID,
 Convert(int, Substring(S.AwfulMultivalue, V.Value * 6 - 3, 3) Settings
 FROM
 SourceTable S
 INNER JOIN master.dbo.spt_values V
 ON V.Value BETWEEN 1 AND Len(S.AwfulMultivalue) / 6
 WHERE
 V.type = 'P'
)
SELECT
 E.Whatever,
 D.Whatever
FROM
 EquipmentSettings E
 INNER JOIN DestinationTable D
 ON E.EquipmentID = D.EquipmentID
 AND E.Settings = D.Settings
</code></pre>

In SQL Server 2005+ this query will support 1365 values in the string.

If the length of the digits can vary, then it's a little harder. Let me know.

I'm working with a database, where one of the fields I extract is something like:

<pre><code>1-117 3-134 3-133
</code></pre>

Each of these number sets represents a different set of data in another table. Taking <code>1-117</code> as an example, <code>1</code> = equipment ID, and <code>117</code> = equipment settings. 

I have another table from which I need to extract data based on the previous field. It has two columns that split equipment ID and settings. Essentially, I need a way to go from the queried column <code>1-117</code> and run a query to extract data from another table where 1 and 117 are two separate corresponding columns.

So, is there anyway to split this number to run this query?

Also, how would I split those three numbers (<code>1-117 3-134 3-133</code>) into three different query sets? 

The tricky part here is that this column can have any number of sets here (such as <code>1-117 3-133</code> or <code>1-117 3-134 3-133 2-131</code>).

I'm creating these queries in a stored procedure as part of a larger document to display the extracted data.

Thanks for any help.

Splitting text in SQL Server stored procedure

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在使用一个数据库，我提取的其中一个字段类似于：1-117 3-134 3-133这些数字集合中的每一个都代表另一个表中的不同数据集合。以1-117为例，1 =设备ID，117 =设备设置。我有另一个表，我需要根据前面的字段从该表中提取数据。它有两列，分别用于拆分设备ID和设置。本质上，我需要一种方法从查询的列1-1...

问拆分SQL Server存储过程中的文本
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拆分SQL Server存储过程中的文本EN