Click here to Skip to main content
14,694,018 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Comments
Maciej Los 14-Aug-13 2:01am
   
If i understand you well, you want to reject /11 part. Correct?

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
   
Comments
ridoy 14-Aug-13 3:22am
   
what an answer,5ed!
Maciej Los 14-Aug-13 3:28am
   
Thank you, Shuvo ;)
Please try the below logic...

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


Happy coding

Regards
Sebastian
   
v2
Comments
ridoy 14-Aug-13 3:23am
   
+4
Declare @val varchar(100) ='LGTF/B203/11'
SELECT SUBSTRING(@val, 1, LEN(@val)- CHARINDEX('/', REVERSE(@val))) AS [Result] 

Happy Coding!
:)
   
v2
Comments
ridoy 14-Aug-13 3:23am
   
+4
Aarti Meswania 14-Aug-13 3:56am
   
Thank you,ridoy
:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900