如何使用sql server拆分字符串

本文介绍了如何使用sql server拆分字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在数据库记录中如下;

LGTF / B203 / 11

i想拆分以上字符串如下

LGTF / B203

我该如何使用sql server。

问候，

Narasiman P.

In database record as follows;
LGTF/B203/11

i want to split the above string as follows
LGTF/B203

for that how can i do using sql server.

Regards,
Narasiman P.

推荐答案

DECLARE @tmp TABLE (ID INT IDENTITY(1,1), SomeText VARCHAR(30))

INSERT INTO @tmp (SomeText)
SELECT 'LGTF/B203/11'
UNION ALL SELECT 'BGFF/B204/A/11'
UNION ALL SELECT 'GFTJ/B204/B/11'
UNION ALL SELECT 'LGTF/B204/C/11'
UNION ALL SELECT 'LATF/B205/11'
UNION ALL SELECT 'LSTF/B206/11'
UNION ALL SELECT 'GGTF/B207/11'
UNION ALL SELECT 'BGTF/B208/11'

;WITH CTE AS
(
	SELECT ID, SomeText, CHARINDEX('/', SomeText) AS StartLocation
	FROM @tmp
	WHERE CHARINDEX('/', SomeText)>0
	UNION ALL
	SELECT ID, SomeText, CHARINDEX('/', SomeText, StartLocation+1) AS StartLocation
	FROM CTE
	WHERE CHARINDEX('/', SomeText, StartLocation+1)>0
)
SELECT ID, SomeText, LEFT(SomeText, StartLocation -1) AS SplitedText
FROM (
	SELECT ID, SomeText, MAX(StartLocation) AS StartLocation
	FROM CTE
	GROUP BY ID, SomeText
) AS T
ORDER BY ID

结果：

Result:

ID  SomeText        SplitedText
1   LGTF/B203/11    LGTF/B203
2   BGFF/B204/A/11  BGFF/B204/A
3   GFTJ/B204/B/11  GFTJ/B204/B
4   LGTF/B204/C/11  LGTF/B204/C
5   LATF/B205/11    LATF/B205
6   LSTF/B206/11    LSTF/B206
7   GGTF/B207/11    GGTF/B207
8   BGTF/B208/11    BGTF/B208

SELECT RIGHT(Code, CHARINDEX('/', REVERSE('/' + Code)) - 1) AS [Result] FROM   YourTable

快乐编码

问候

Sebastian

Happy coding

Regards
Sebastian

Declare @val varchar(100) ='LGTF/B203/11'
SELECT SUBSTRING(@val, 1, LEN(@val)- CHARINDEX('/', REVERSE(@val))) AS [Result]

快乐编码！

：）

Happy Coding!
:)

这篇关于如何使用sql server拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！