从sql列值获取子字符串 [英] Getting substring from sql column value

问题描述

我在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) 

这篇关于从sql列值获取子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！