Link to home
Create AccountLog in
Avatar of Vishal Jaiswal
Vishal JaiswalFlag for United States of America

asked on

parse a varchar string seperated by semi colon.

parse a string . I have a column that is varchar column. want to parse all the value in column: for example:

data is like below:


Applicationname:Giv Framework-Automation;Client: Ceq Internal; Department:Azure Sql; city:Newyork


Output expected :

Applicationname                 Client        Department  city

Giv Framework-Automation  Ceq Internal  Azure Sql   Newyork


So, basically the big string contains multiple things and all are semicolumn seperated. want to break into different columns.  

If possible at least give me below that also help:


Applicationname:Giv Framework-Automation

Client: Ceq Interna

Department:Azure Sql

city:Newyork


After that I believe, I will use pivot to convery them into column. Please help me to parse that string. in the above one have 4 values.. may be few have 3 or 5 or 6. but they all are semocolon seperated



Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Exchange the WITH clause with your table source(s) accordingly...

with test_table as
 (select 'Applicationname:Giv Framework-Automation;Client: Ceq Internal; Department:Azure Sql; city:Newyork' col
    from dual)
select trim(substr(b1.column_value, instr(b1.column_value, ':') + 1)) "Applicationname",
       trim(substr(b2.column_value, instr(b2.column_value, ':') + 1)) "Client",
       trim(substr(b3.column_value, instr(b3.column_value, ':') + 1)) "Department",
       trim(substr(b4.column_value, instr(b4.column_value, ':') + 1)) "city"
  from test_table a
  join lateral (select *
                  from apex_string.split(a.col, ';')) b1 on (trim(b1.column_value) like 'Applicationname%')
  join lateral (select *
                  from apex_string.split(a.col, ';')) b2 on (trim(b2.column_value) like 'Client%')
  join lateral (select *
                  from apex_string.split(a.col, ';')) b3 on (trim(b3.column_value) like 'Department%')
  join lateral (select *
                  from apex_string.split(a.col, ';')) b4 on (trim(b4.column_value) like 'city%');

Open in new window


Avatar of Vishal Jaiswal

ASKER

@Alex [***Alex140181***]

ORA-00904: "APEX_STRING"."SPLIT": invalid identifier

00904. 00000 -  "%s: invalid identifier"

*Cause:    

*Action:

Error at Line: 10 Column: 24

ASKER CERTIFIED SOLUTION
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
the go to for splitting is the 8k splitter:
https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function

CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

Open in new window


with that you can:
select * into #temp from [dbo].[DelimitedSplit8K](@string, ';'); select * from #temp

Open in new window


From there you can use a dynamic pivot to break those rows into columns:
https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query