Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SadafKhan85
Starting Member
8 Posts |
 Posted - 2013-07-03 : 07:45:12
|
| 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)
17689 Posts |
Posted - 2013-07-03 : 08:17:13
|
VARCHAR2 ?This does not looks like is for Microsoft SQL Server
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
SadafKhan85
Starting Member
8 Posts |
Posted - 2013-07-06 : 03:32:40
|
| It is Microsoft SQL Server query. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-07-06 : 04:28:20
|
Really ? Which version of SQL Server it runs on ?I get the following error when execute on SQL Server 2008 R2 and 2012quote:
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 19Incorrect syntax near 'loop'.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-07 : 10:22:51
|
| I dont think it is T-SQL := etc are not operators in sql server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Parsing delimited (,) string