T-SQL 获取数据库中每行的第 5 个单词

2021-09-10 00:00:00 sql tsql sql-server

我需要找出如何编写 T-SQL 来获取数据库中每一行中的第 5 个单词

I need to find out how to code T-SQL that gets the 5th word in each row in a database

推荐答案

确保顺序的一个选项是使用一点 XML

One option which will ensure sequence is to use a bit of XML

示例

Declare @YourTable table (ID int, SomeCol varchar(100))
Insert into @YourTable Values
 (1,'Some text that needs to be parsed')
,(2,'Only three words')

Select A.ID
      ,Pos5  =  cast('<x>' + replace(SomeCol,' ','</x><x>')+'</x>' as xml).value('/x[5]','varchar(50)')
 From  @YourTable A

退货

ID  Pos5
1   to
2   NULL

相关文章