SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parsing delimited (,) string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SadafKhan85
Starting Member

India
8 Posts

Posted - 07/03/2013 :  07:45:12  Show Profile  Reply with Quote
My below query will help in parsing any delimited string (in above example it is using ',' as parsing delimiter. This query can be useful in many business scenarios where in we have input data as a long string containing delimited values.

declare
v_sql VARCHAR2(2000);
v_pos INTEGER;
v_differentiator VARCHAR2(10);
v_sql_temp VARCHAR2(1000);
v_pos_temp INTEGER;
begin
v_pos := 1;
v_sql := 'a,abc,abcde,ab,sos,sooooo,oooops,posh,';
v_differentiator := ',';
v_sql_temp := v_sql;
while (instr (v_sql,v_differentiator,v_pos) > 0 )
loop
v_pos_temp := instr(v_sql,v_differentiator,v_pos );
v_sql_temp := substr(v_sql,v_pos,(v_pos_temp-v_pos));
v_pos := v_pos_temp +1;
--dbms_output.put_line (v_sql_temp);
insert into temp_comma(col1) values (v_sql_temp);
end loop;
end;

khtan
In (Som, Ni, Yak)

Singapore
17661 Posts

Posted - 07/03/2013 :  08:17:13  Show Profile  Reply with Quote
VARCHAR2 ?

This does not looks like is for Microsoft SQL Server


KH
Time is always against us

Go to Top of Page

SadafKhan85
Starting Member

India
8 Posts

Posted - 07/06/2013 :  03:32:40  Show Profile  Reply with Quote
It is Microsoft SQL Server query.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17661 Posts

Posted - 07/06/2013 :  04:28:20  Show Profile  Reply with Quote
Really ? Which version of SQL Server it runs on ?

I get the following error when execute on SQL Server 2008 R2 and 2012
quote:

Server: Msg 155, Level 15, State 2, Line 2
'VARCHAR2' is not a recognized CURSOR option.
Server: Msg 195, Level 15, State 1, Line 12
'instr' is not a recognized built-in function name.
Server: Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'loop'.




KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/07/2013 :  10:22:51  Show Profile  Reply with Quote
I dont think it is T-SQL := etc are not operators in sql server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000