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?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Sign up for a free trial of Coupler.io - The No code data integration tool
app.coupler.io...
Get USD 100 off Coursera Plus Annual Subscription
imp.i384100.ne...
Get 50% off Skillshare Annual Subscription with code AFF50.
Dates: 11/24 at midnight - 11/28 at midnight
skillshare.eqc...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.ne...
Beginners to Python Programming
skillshare.eqc...
Data Science and Business Analytics with Python
skillshare.eqc...
Get 40% OFF of Skillshare with code FLASH40 - October 2022
skillshare.eqc...
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.ne...
Python for Everybody Specialization
imp.i384100.ne...
Google Data Analytics Professional Certificate
imp.i384100.ne...
Coursera Plus - Data Science Career Skills
imp.i384100.ne...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.c...
Email - [e-mail]

[@sukumar44]

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.

[@LearnatKnowstar]

Thank you so much. Sure. Will post a video soon.

[@tarvinder91]

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

[@LearnatKnowstar]

Thank you

[@vijay00071]

CHARINDEX is not working on my SQL

[@AJITHkumar-lj7gp]

Try instring function

[@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.

[@YouCallThataKnife253]

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

[@solomong.gebrhana1204]

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

[@kapach1000]

❤ love this explanation.

[@LearnatKnowstar]

Thank you! Glad you found it helpful.

[@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 😊!

[@LearnatKnowstar]

There can be different approaches. One way can be to use string split function. Please see detailed tutorial here -
th-cam.com/video/4pdNjE98R3Q/w-d-xo.html

[@lia_sahin]

@@LearnatKnowstar thanks 😊

[@pareshyadnik4108]

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

[@suryakota7685]

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

[@LearnatKnowstar]

Glad to hear that

[@Beyondhorizons-i5z]

Brilliant stuff!

[@LearnatKnowstar]

Thank you

[@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..

[@Dracometeor562]

is this supported at SQL 2005 ?

[@LearnatKnowstar]

Yes, it is.

[@kitbodeephongkasem909]

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

[@latapant6371]

Very useful video 🙏🙏

[@LearnatKnowstar]

Thank you

[@Manish247-R]

please tell me for 2 delimiters in between.

[@LearnatKnowstar]

You can try using the Split String function.
Please find below the video tutorial for the same -
th-cam.com/video/4pdNjE98R3Q/w-d-xo.html

[@shubhamgoyal5227]

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.

[@hnaidu.pro21]

Static Method (Name is Hardcoded):
SELECT SUBSTRING('Ram Kumar Bansal',
CHARINDEX(' ', 'Ram Kumar Bansal') + 1,
CHARINDEX(' ', 'Ram Kumar Bansal', 5) - CHARINDEX(' ', 'Ram Kumar Bansal', 1)) AS MiddleName
Dynamic Method (Applies to all names):
SELECT SUBSTRING([Name],
CHARINDEX(' ', [Name]) + 1,
CHARINDEX(' ', [Name], CHARINDEX(' ', [Name]) + 1) - CHARINDEX(' ', [Name], 1)) AS MiddleName
FROM TableName;

[@tanyagupta6319]

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?

[@Faisal1504]

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

[@LearnatKnowstar]

Yes, you can do a +1 or apply trim on the extracted first name.

[@lzxp7943]

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

[@LearnatKnowstar]

Thank you so much!

[@edgetransit3320]

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

[@LearnatKnowstar]

Glad I could help!

[@abhishekrawat8305]

please provide DDL and DML commands also

[@HappyModernW3]

Very helpful!!

[@LearnatKnowstar]

Thank you

[@NaveenKumar-fq4sb]

where is data set ...

[@sanujidananjaya]

Thank you ! , Very usefull

[@LearnatKnowstar]

Thank you

[@prashantx19]

what if there are three commas

[@LearnatKnowstar]

You can use string split function. See tutorial here
th-cam.com/video/4pdNjE98R3Q/w-d-xo.html

[@prashantx19]

@@LearnatKnowstar thanks ... it really helpful.

[@raj-sk4br]

What about middle name?

[@LearnatKnowstar]

If there is always a middle name, you can use charindex to extract the middle string. If the middle name might or might not exist, then more logic needs to be put in the SQL query.

[@LearnatKnowstar]

Thanks for presenting this scenario. We will post a solution video soon.

[@TheDistractionStudio]

Thank you!!

[@LearnatKnowstar]

Thank you

[@prashantdahiya711]

Thank you Mam, 👍

[@LearnatKnowstar]

Glad it was helpful 🙏

[@avinashkarad7141]

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

[@landchennai8549]

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

[@hnaidu.pro21]

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]