前端之家

  1. 首页
  2. 问答

SQL Server根据模式拆分字符串

• 问答

我正在尝试基于两种模式来拆分特定的字符串，第一个为最后一个字符，第一个字符必须为任何数字值，最后一个为逗号。

我正在使用patindex，substring和charindex函数，但它们只给出一行。

这是我正在处理的查询：

DeclARE @x varchar(max) = 'In the vocational certificate category,if served as laundry/dry cleaning specialist,2 semester hours in treatment of fabrics (stain removing,spotting,solutions),2 in laundry equipment operation,2 in dry cleaning,3 in small business management,3 in introduction to business,3 in supply management,and 3 in business report writing.'
   Declare @extract varchar(max) = ''
   set @extract = substring(@x,patindex('%[0-99] [i]%[,] %',@x),(LEN(@x)) -  CHARINDEX('%[0-99] [i]%[,REVERSE(@x))) 
   select @extract

结果是这样的： 洗衣设备操作2个，干洗2个，小型企业管理3个，企业介绍3个，供应管理3个，业务报告撰写3个。

有什么方法可以获取列中的结果？像这样：

  • 2在洗衣设备操作中
  • 2干洗
    • 小型企业管理中的
  • 3

...

nanalover 回答：SQL Server根据模式拆分字符串

虽然我同意sql server是适用于该工作的错误工具，但它是可行的。您可以使用DelimitedSplit8K_LEAD函数，该函数可以在此处找到。 https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2

然后处理这个问题很简单。请注意，这不会返回“业务报告撰写中的3”，因为这不符合您的“最后一个值是逗号”的规则。

DECLARE @x varchar(1000) = 'In the vocational certificate category,if served as laundry/dry cleaning specialist,2 semester hours in treatment of fabrics (stain removing,spotting,solutions),2 in laundry equipment operation,2 in dry cleaning,3 in small business management,3 in introduction to business,3 in supply management,and 3 in business report writing.'

select MyResult = ltrim(s.Item)
from dbo.DelimitedSplit8K_LEAD(@x,',') s
where left(ltrim(s.Item),1) like '%[0-9]%'
order by s.ItemNumber

以上查询将返回。

2 semester hours in treatment of fabrics (stain removing
2 in laundry equipment operation
2 in dry cleaning
3 in small business management
3 in introduction to business
3 in supply management
design-patternsfunctionsplitsql-serverstring
本文链接：https://www.f2er.com/3163494.html

大家都在问