从sql列值获取子字符串

本文介绍了从sql列值获取子字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在sql表中有一个数据类型为varchar的列，我想从该列获取子字符串 例如我有一个像 ID |的数据工作名称 1 | 11-92-23-04 2 | 12-13-04 3 | 12-763-07 4 | 12-67 从这些我希望获得第二个破折号的名单，如 11-92 12-13 12-763 12-67

I have a column in a sql table having datatype varchar,I want to get substring from that column e.g. I have a data like ID | Job Name 1 | 11-92-23-04 2 | 12-13-04 3 | 12-763-07 4 | 12-67 From these I want to get list seperated by 2nd dash like 11-92 12-13 12-763 12-67

推荐答案

如果您想从中获取零件字符串，使用它： If you would like to get parts from string, use this: DECLARE @tmp TABLE (ID INT IDENTITY(1,1), [Job Name] VARCHAR(30)) INSERT INTO @tmp ([Job Name]) SELECT '11-92-23-04' UNION ALL SELECT '12-13-04' UNION ALL SELECT '12-763-07' UNION ALL SELECT '12-67' ;WITH Parts AS ( SELECT 1 AS PartNo, ID, LEFT([Job Name], CHARINDEX('-',[Job Name])-1) AS Part, RIGHT([Job Name], LEN([Job Name])- CHARINDEX('-',[Job Name])) AS Remainder, [Job Name] FROM @tmp WHERE CHARINDEX('-',[Job Name])>0 UNION ALL SELECT PartNo+1 AS PartNo, ID, LEFT([Remainder], CHARINDEX('-',[Remainder])-1) AS Part, RIGHT([Remainder], LEN([Remainder])- CHARINDEX('-',[Remainder])) AS Remainder, [Job Name] FROM Parts WHERE CHARINDEX('-',[Remainder])>0 UNION ALL SELECT PartNo+1 AS PartNo, ID, Remainder AS Part, NULL AS Remainder, [Job Name] FROM Parts WHERE CHARINDEX('-',[Remainder])=0 ) SELECT ID, PartNo, Part, [Job Name] FROM Parts WHERE PartNo<3 ORDER BY ID, PartNo

结果：

Result:

ID PartNo Part [Job Name] 1 1 11 11-92-23-04 1 2 92 11-92-23-04 2 1 12 12-13-04 2 2 13 12-13-04 3 1 12 12-763-07 3 2 763 12-763-07 4 1 12 12-67 4 2 67 12-67

一种方法是编写一个使用RegularExpression的CLR函数。 使用直接SQL复杂的字符串操作很快变得乏味。 一个更好的想法是将数据放入你想要的方式 out 。 One way is to write a CLR function that uses a RegularExpression. Using straight SQL for complex string manipulation quickly become tedious. A better idea is to put the data in the way you want to get it out.

试试这段代码 Try this code select SUBSTRING(JobName,0,CHARINDEX ('-',JobName,CHARINDEX ('-',JobName,0)+1))

SELECT SUBSTRING(JobName, 0, case WHEN CHARINDEX('-', JobName, CHARINDEX('-', JobName, 0) + 1) > 0 THEN CHARINDEX('-', JobName, CHARINDEX('-', JobName, 0) + 1) ELSE len(JobName) + 1 end ) FROM ScheduleMaster)