How to access the substring from the string in sql?

SELECT LEFT(SUBSTRING('Hello World in C',
CHARINDEX(' ', 'Hello World in C') + 1, 100),
CHARINDEX(' ', 'Hello World in C') - 1)

You can do the required task using SUBSTRING string function, here are the few example

Get the First Name and Last Name from a Full Name

DECLARE @FullName    VARCHAR(50) = 'Vikas Lalwani'

SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [First Name],
       SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [Last Name]

Output

First Name    Last Name
------------  ------------
Vikas             Lalwani

 Get the First and Last Word from a String or Sentence using Substring in SQL

DECLARE @Sentence    VARCHAR(MAX) = 'The quick brown fox jumps over the lazy dog'

SELECT SUBSTRING(@Sentence, 1, CHARINDEX(' ', @Sentence) - 1) AS [First Word],
       REVERSE(SUBSTRING(REVERSE(@Sentence), 1, 
               CHARINDEX(' ', REVERSE(@Sentence)) - 1)) AS [Last Word]

Output:

First Word   Last Word
------------ -----------
The          dog

Convert a Comma-Delimited List to a Table

DECLARE @StringInput  VARCHAR(100) = 'Monday,Tuesday,Wednesday'
DECLARE @StringValue  VARCHAR(100)
DECLARE @OutputTable TABLE (
    [StringValue]     VARCHAR(100)
)

WHILE LEN(@StringInput) > 0
BEGIN
    SET @StringValue = LEFT(@StringInput, 
                            ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                            LEN(@StringInput)))
    SET @StringInput = SUBSTRING(@StringInput,
                                 ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                 LEN(@StringInput)) + 1, LEN(@StringInput))

    INSERT INTO @OutputTable ( [StringValue] )
    VALUES ( @StringValue )
END

SELECT * FROM @OutputTable

Output:

StringValue
-------------
Monday
Tuesday
Wednesday

Source