SQL - How do I split a string based on a delimiter?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

, +

itemcode

SER_ELECTR_003
SER_MACHRENTL_004
CI_BRI_0005
CI_CEM_00001
This is my column

1)REQUIRED OUTPUT

ITEMCODE             PART1  PART2     PART3
SER_ELECTR_003       SER    ELECT      003
SER_MACHRENTL_004    SER   MACHRENTL   004
CI_BRI_00005          CI     BRI      0005
CI_CEM_00001          CI     CEM      00001

Posted 23-Aug-15 23:12pm

Member 11917248

Updated 22-Aug-18 21:16pm

Duncan Edwards Jones

v6

Add a Solution

Comments

Sanket Saxena 24-Aug-15 5:20am

Why?

Andy Lanng 24-Aug-15 5:24am

Don't shout. It is considered rude and will prompt people to flag the question.

As it stands, your question is not complete. Take some time and use the "Improve Question" button to add some more detail. Do not repost.

Thanks
Andy

4 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Duncan Edwards Jones · Answer 1 · 2015-08-23T23:28:00

First creaste a generic "split"[^] function, like so:-

SQL

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO

Then use it in your query e.g. like:-

SQL

select *
from fnSplit('SER_ELECTR_003', '_')

Maciej Los · Answer 2 · 2015-08-23T23:58:00

Solution 2

As to OP's comment:

Member 11917248 wrote:
IS IT NOT POSSIBLE TO SOLVE IN A SIMPLE WAY WITHOUT USING FUNCTION

Yes, it is posible to get result using CTE: get 2nd and 3rd string by splitting a long string using T-Sql[^]

Posted 23-Aug-15 23:58pm

Maciej Los

kirthiga S · Answer 3 · 2018-08-22T21:17:00

Solution 4

Using Cte it is possible

DECLARE @table TABLE(Keywords varchar(100))
insert into @table values('SER_ELECTR_003')
;with cte as (
select Keywords,substring(Keywords,0,charindex('_',Keywords))Key1 
	,SUBSTRING(Keywords,charindex('_',Keywords)+1,LEN(Keywords)-charindex('_',Keywords))Key2
from @table
)
select Keywords,Key1,substring(Key2,0,charindex('_',Key2))Key2
	,SUBSTRING(Key2,charindex('_',Key2)+1,LEN(Key2)-charindex('_',Key2))Key3
from cte

Posted 22-Aug-18 21:17pm

kirthiga S

Comments

Nelek 23-Aug-18 6:55am

NO REPORT NEEDED HERE:

This user has answered without looking at the date, but is nothing related to abuse. The "non-answer" that has brought this old question back to the light was the #3 (it will get nuked soon)

DON'T REPORT THIS SOLUTION, SINCE IT IS FULLY LEGITIMATE (correct or wrong, I don't mix me in that)

Nelek 23-Aug-18 6:57am

@Kirthiga S, just in case you wonder... my other comment is to protect you and your answer of being considered abuse (reputation hunting)

Member 13958363 · Answer 4 · 2018-08-22T16:38:00

Solution 3

Hi can it be done in column wise?

Posted 22-Aug-18 16:38pm

Member 13958363

SQL - How do I split a string based on a delimiter?

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

SQL - How do I split a string based on a delimiter?

4 solutions

Solution 1

Solution 2

Solution 4

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us