CODEWITHSUNDEEP
sql-servert-sql
jackCaller
jackCaller

Reputation: 205

Get the values from a column with string delimeter

I just want to know how can i get the values using select from a column with string delimeter.

Ex: column value is:

    ';#10;~Banana;#101;~Apple'

How can i get these values and save in a table like this?

    code   description
    10     Banana
    101    Apple

can't seem to get it using substring, charindex combo. Should i use function instead? Basically, this column has a potential to store 100+ values (code/desc).

Upvotes: 1

Views: 185

Answers (3)

BlueConga
BlueConga

Reputation: 917

Or simple substring by ';'

select 
   substr( column, INSTR(column, ';', 1, 1)+1, 
   (INSTR(column, ';', 1, 2 ) - INSTR(column, 1, 1 ))
   ) col_a,
   substr( column, INSTR(column, ';', 1, 2)+1, 
   (INSTR(column, ';', 1, 3 ) - INSTR(column, ';', 1, 2 ))
   ) col_b
from dual

Working example:

select 
   substr( ';#10;~Banana;#101;~Apple', INSTR(';#10;~Banana;#101;~Apple', ';', 1, 1)+1, 
    (INSTR(';#10;~Banana;#101;~Apple', ';', 1, 2 ) - INSTR(';#10;~Banana;#101;~Apple', ';', 1, 1 ))
   ) col_a,
   substr( ';#10;~Banana;#101;~Apple', INSTR(';#10;~Banana;#101;~Apple', ';', 1, 2)+1, 
   (INSTR(';#10;~Banana;#101;~Apple', ';', 1, 3 ) - INSTR(';#10;~Banana;#101;~Apple', ';', 1, 2 ))
   ) col_b
from dual

Upvotes: 0

Kaf
Kaf

Reputation: 33849

Try this using XML Nodes method (SQL-FIDDEL)

DECLARE @S VARCHAR(500)= ';#10;~Banana;#101;~Apple'

--REMOVE LEADING ROW DELIMETER `;#`
SELECT @S = SUBSTRING(@S,3, LEN(@S))


DECLARE @X XML 
SELECT @X  = '<myxml><nodes><n1>' + 
REPLACE(REPLACE (@S,';#','</n2></nodes><nodes><n1>'),';~','</n1><n2>') + 
'</n2></nodes></myxml>'


SELECT C.value('n1[1]','VARCHAR(50)') AS code,
       C.value('n2[1]','VARCHAR(50)') AS description
FROM @X.nodes('/myxml/nodes') Cols (C)


| CODE | DESCRIPTION |
----------------------
|   10 |      Banana |
|  101 |       Apple |

Upvotes: 2

kostas ch.
kostas ch.

Reputation: 2035

You can try the above (instead of ',' use ';'):

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



select * from dbo.fnSplit('1,22,333,444,,5555,666', ',')

Results

Item

1
22
333
444
5555
666

Upvotes: 0

Related Questions