如何使用sql server拆分字符串 [英] how to split the string using 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.

推荐答案

请阅读我的评论关于这个问题。



看看例子：

Please, read my comment to the question.

Have a look at example:

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

请尝试以下逻辑...

Please try the below logic...

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拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！