SQL Query | Split Concatenated String into Columns | CharIndex

Hello,

This video explains the use of CharIndex function in SQL Server to extract data from a concatenated string into different columns.

The second part of this video will explain the use of another SQL function that can be used to achieve the same results.

How to install SQL Server for practice?

Check out the complete list of SQL Query Interview Questions -

Get USD 100 off Coursera Plus Annual Subscription

Get 50% off Skillshare Annual Subscription with code AFF50.
Dates: 11/24 at midnight - 11/28 at midnight

Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization

Beginners to Python Programming

Data Science and Business Analytics with Python

Get 40% OFF of Skillshare with code FLASH40 - October 2022

Data Science Fundamentals with Python and SQL Specialization

Python for Everybody Specialization

Google Data Analytics Professional Certificate

Coursera Plus - Data Science Career Skills

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[tarvinder]

Thank u for explaining that embedded queries. I was able to use that knowledge in other queries.

[sukumar]

This is what I was looking for so many days. Finally landed to the right place. Thank you. Can you also show the same with the help of Substr() function.

[suryakota]

Thank you so much, for your explanation, i was asked this question in one of Big MNC

[edgetransit]

You saved my life lmfao. Thank you so much !!

[pickmeisha]

My field is 'Doe, John A' and I am trying to partition the A...this is not working as it pulls over the entire name when there is no middle initial.

[lzxp]

thank you very much for break it down to step by step

[YouCallThataKnife]

What if I no longer want to see that original concatenated column?

[pareshyadnik]

can we use parse name function to split the string or there is a performance issue

[lia_sahin]

how to split a large table consisting of a single column with 15 attributes, there should be 15 columns. in MsSQL server. Thanks 😊!

[solomong.gebrhana]

what if we have 4 strings to extract? what is the best function to use?

[tanyagupta]

How many possibilities these SQL queries will come in the placement? I mean same as it came ya little bit differ has to be come?

[Hakeem_Abdul]

Can you pls help me with a similar request.?? My need is: if length of input field is more than 40 characters, then it should load data until last space before 40 characters to one column and the remaining should load to another column..

[abhishekrawat]

please provide DDL and DML commands also

[shubhamgoyal]

Hello mam if there is middle name then then could you please guide how to derive it e.g ram kumar bansal in this how can we derive kumar using sql query.

[Faisal]

What about the space between the, and first name? Don’t we add +1 to charIndex. ?

[kitbodeephongkasem]

If.​ Colum.​​name.​ some.row.​ Not​ have​ comma?

[Manishchandrrnjn]

please tell me for 2 delimiters in between.

[landchennai]

select *
from
(
select * from #Emp
cross apply string_split(Name, ', ', 1)
) v
pivot(max(value) for ordinal in ([1], [2]))v

[avinashkarad]

i am using 11g .. i have data like below in 1 column
col_name
1
john
34000
3
david
20000

want output like in 3 different columns like this
id name salary
1 john 34000
3 david 20000

pls help me with this @Learn at Knowstar

[hnaidu.pro]

Another Method: Using a Substring

SELECT EmployeeID, Name,
SUBSTRING([Name], CHARINDEX(' ', [Name], 1) + 1, LEN([Name])) AS FirstName,
SUBSTRING([Name], 1, CHARINDEX(', ', [Name], 1) - 1) AS LastName
FROM [dbo].[tblNames]

Method 2: Using Reverse function (along with LEFT, RIGHT)

SELECT EmployeeID, Name,
RIGHT([Name], CHARINDEX(' ', REVERSE([Name])) - 1) AS FirstName,
LEFT([Name], CHARINDEX(', ', [Name], 1) - 1) AS LastName
FROM [dbo].[tblNames]