How to access the substring from the string in sql?


I need to access substring from a string using SQL, how can i achieve it in SQL server?

Suppose my string in "Hello World in C" and i want "World" from it, how can i do that using SQL query?


Asked by:- jaiprakash
0
: 584 At:- 11/2/2017 6:46:10 AM
sql sub-string from string split string in sql sql-split-string-by-delimiter






2 Answers
profileImage Answered by:- user_1953849261

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

2
At:- 11/2/2017 7:32:50 AM
thanks, great answer, I executed this query in SQL server and worked as needed, I see CharIndex is used to take Index of element in a string 0
By : jaiprakash - at :- 11/2/2017 3:59:44 PM


profileImage Answered by:- vikas_jk

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

1
At:- 11/4/2017 11:55:56 AM Updated at:- 11/4/2017 11:56:33 AM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use