SQL Query | Split Concatenated String into Columns | CharIndex

[@vijay00071]

CHARINDEX is not working on my SQL

[@AJITHkumar-lj7gp]

Try instring function

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

[@kapach1000]

❤ love this explanation.

[@LearnatKnowstar]

Thank you! Glad you found it helpful.

[@suryakota7685]

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

[@LearnatKnowstar]

Glad to hear that

[@pareshyadnik4108]

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

[@LearnatKnowstar]

There can be different approaches. One way can be to use string split function. Please see detailed tutorial here - kzbin.info/www/bejne/aqHHf517bp2FabM

[@lia_sahin]

@@LearnatKnowstar thanks 😊

[@Dracometeor562]

is this supported at SQL 2005 ?

[@LearnatKnowstar]

Yes, it is.

[@YouCallThataKnife253]

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

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

[@solomong.gebrhana1204]

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

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

[@lzxp7943]

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

[@LearnatKnowstar]

Thank you so much!

[@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 - kzbin.info/www/bejne/aqHHf517bp2FabM

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

[@abhishekrawat8305]

please provide DDL and DML commands also

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

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

[@prashantx19]

what if there are three commas

[@LearnatKnowstar]

You can use string split function. See tutorial here kzbin.info/www/bejne/aqHHf517bp2FabM

[@prashantx19]

@@LearnatKnowstar thanks ... it really helpful.

[@edgetransit3320]

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

[@LearnatKnowstar]

Glad I could help!

[@kitbodeephongkasem909]

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

[@Beyondhorizons-i5z]

Brilliant stuff!

[@LearnatKnowstar]

Thank you

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

[@sanujidananjaya]

Thank you ! , Very usefull

[@LearnatKnowstar]

Thank you

[@NaveenKumar-fq4sb]

where is data set ...

[@prashantdahiya711]

Thank you Mam, 👍

[@LearnatKnowstar]

Glad it was helpful 🙏

[@HappyModernW3]

Very helpful!!

[@LearnatKnowstar]

Thank you

[@landchennai8549]

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

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

[@TheDistractionStudio]

Thank you!!

[@LearnatKnowstar]

Thank you

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