--USE CHARINDEX TO GET THE LEFT VALUE FROM STRING (i.e '12345: ABCD EFGH IJK')
DECLARE @t1 as varchar (50);
SET @t1 = '12345: ABCD EFGH IJK'
SELECT LEFT(@t1, CHARINDEX (':' , @t1) -1)
EXPECTED RESULT:12345
--USE CHARINDEX TO GET THE RIGHT VALUE FROM STRING (i.e '12345: ABCD EFGH IJK')
DECLARE @t2 as varchar (50);
SET @t2 = '12345: ABCD EFGH IJK'
SELECT RIGHT(@t2, LEN(@t2) - CHARINDEX (':' , @t2) )
EXPECTED RESULT: ABCD EFGH IJK
--USE CHARINDEX TO SEPERATE THE LEFT,RIGHT,CENTER, VALUE FROM SINGLE STRING (i.e '12345/ABCD/BULITASKING')
DECLARE @t3 as varchar (50);
SET @t3 = '12345/ABCD/BULITASKING'
SELECT
CASE
WHEN CHARINDEX('/', @t3) > 0 THEN LTRIM(RTRIM(SUBSTRING(@t3, 1, CHARINDEX('/', @t3)-1)))
ELSE
@t3
END AS [Column 1]
,CASE WHEN CHARINDEX('/', @t3) > 0 THEN LTRIM(RTRIM(SUBSTRING(RIGHT(@t3, LEN(@t3) - CHARINDEX ('/' , @t3) ), 1, CHARINDEX('/', RIGHT(@t3, LEN(@t3) - CHARINDEX ('/' , @t3) ))-1)))
ELSE
@t3
END AS [Column 2]
,CASE WHEN CHARINDEX('/', @t3) > 0 THEN RIGHT(RIGHT(@t3, LEN(@t3) - CHARINDEX ('/' , @t3) ), LEN(RIGHT(@t3, LEN(@t3) - CHARINDEX ('/' , @t3) )) - CHARINDEX ('/' , RIGHT(@t3, LEN(@t3) - CHARINDEX ('/' , @t3) )))
ELSE
@t3
END AS [Column 3]