This content has been marked as final.
Show 30 replies

1. Re: How to concatnate the string in SQL or PL/SQL?
vijayrsehgalOracle May 18, 2012 10:36 AM (in response to 634151)something like below could help you, but this is with reference to your values, you need to enhance it further if you have data coming from table.
Regards,select replace('1.0.0.C.D','.0.0','.a.b') from dual
Vijay
Edited by: user10302525 on May 18, 2012 3:36 AM 
2. Re: How to concatnate the string in SQL or PL/SQL?
Solomon Yakobson May 18, 2012 10:36 AM (in response to 634151)Use REPLACE:
SY.SQL> select replace('1.0.0.C.D','0.0','A.B') 2 from dual 3 / REPLACE('  1.A.B.C.D SQL>

3. Re: How to concatnate the string in SQL or PL/SQL?
Vivek L May 18, 2012 10:38 AM (in response to 634151)user212310 wrote:
Sorry, but your question is too vague to answer.
Hi,
I have the value like A.B and i have another value 1.0.0.C.D
Is it possible to replace 0.0 with A.B value in SQL or PL/SQL?
The string should be 1.A.B.C.D
Thanks
What values to be replaced? 0.0 in all cases?
How do you get the values ("A.B") to be replaced with? is it A.B in all cases?
A simple replace can do what you are asking for :
But does this meet your requirements?SQL> select replace('1.0.0.C.D','0.0','A.B') from dual; REPLACE('  1.A.B.C.D
Please read the forum FAQ {message:id=9360002}
and provide all necessary details along with a sample test case
Also, always provide your 4 digit Oracle database version 
4. Re: How to concatnate the string in SQL or PL/SQL?
634151 May 19, 2012 10:22 AM (in response to Vivek L)hi,
Thanks for all your response.
The value 0.0 is not stable. Sometimes it will be different too. This 0.0 value is stored in the same table where my entire string is stored 1.0.0.C.D
But my expectation is somewhat completcated here. I mean sometimes we need to replace the value 0.C or 1.C or 1.D also with appropriate value. We will consider that the above string is separated with comma separated value and each values are called as segments. I have another table where i can store the sequence number of the segment. In my example, the sequence number of the value 0.0 is 2,3. At the same time 0.C is 3,4 and 1.C is 1,4 and 1.D is 1,5.
So now whatever value is, it may be 0.0 or 0.C or 1.C or 1.D we have to repalce with the appropriate values. For example.
1.0.0.C.D > 0.0 > 1.A.B.C.D
1.0.0.C.D > 0.C > 1.0.A.B.D
1.0.0.C.D > 1.C > A.0.0.B.D
1.0.0.C.D > 1.D > A.0.0.C.B
1.0.0.C.D > C.D > 1.0.0.A.B
Thanks 
5. Re: How to concatnate the string in SQL or PL/SQL?
Etbin May 19, 2012 10:35 AM (in response to 634151)Maybe
Regardsreplace(replace(your_string,substr(to_replace,1,2),'A.'),substr(to_replace,2),'.B')
Etbin 
6. Re: How to concatnate the string in SQL or PL/SQL?
Biju Das May 19, 2012 5:08 PM (in response to 634151)Forget what you have written here.
Gather the requirement well, what is the objective? Define the set of all rules to meet the requirement and then try to solve the problem. If failed, post your question here describing your requirement well. I am having hard time here understanding your requirement. It seems you are trying to solve something without having enough information.
Regards
Biju
Edited by: biju2012 on May 19, 2012 3:40 AM 
7. Re: How to concatnate the string in SQL or PL/SQL?
634151 May 19, 2012 11:00 AM (in response to Biju Das)Hi Biju,
There is no other way to explain here. I explained fully with clear examples. I cannot explain other than this.
Thanks 
8. Re: How to concatnate the string in SQL or PL/SQL?
Solomon Yakobson May 19, 2012 11:46 AM (in response to 634151)user212310 wrote:
Assuming all strings have 5 dot separated parts:
There is no other way to explain here. I explained fully with clear examples. I cannot explain other than this.
First table:
Second table:SQL> select * 2 from tbl1 3 / STR VAL   1.0.0.C.D 0.0 1.0.0.C.D 0.C 1.0.0.C.D 1.C 1.0.0.C.D 1.D 1.0.0.C.D C.D SQL>
Solution:SQL> select * 2 from tbl2 3 / VAL SEQ   0.0 2,3 0.C 3,4 1.C 1,4 1.D 1,5 C.D 4,5 SQL>
SY.with t1 as ( select val, seq, lvl, case when ','  seq  ',' like '%,'  lvl  ',%' then 1 end ind from tbl2, ( select level lvl from dual connect by level <= 5 ) ), t2 as ( select val, seq, lvl, case ind when 1 then chr(ascii('A') + sum(ind) over(partition by val order by lvl)  1) end new from t1 ) select str, tbl1.val, seq, rtrim(xmlagg(xmlelement(e,nvl(new,regexp_substr(str,'[^.]+',1,lvl)),'.').extract('//text()') order by lvl),'.') new_str from tbl1, t2 where t2.val = tbl1.val group by str, tbl1.val, seq / STR VAL SEQ NEW_STR     1.0.0.C.D 0.0 2,3 1.A.B.C.D 1.0.0.C.D 0.C 3,4 1.0.A.B.D 1.0.0.C.D 1.C 1,4 A.0.0.B.D 1.0.0.C.D 1.D 1,5 A.0.0.C.B 1.0.0.C.D C.D 4,5 1.0.0.A.B SQL>

9. Re: How to concatnate the string in SQL or PL/SQL?
634151 May 19, 2012 12:11 PM (in response to Solomon Yakobson)Hi Solomon,
Thanks for the reply. I have changed the table values bit. It will looks like below.
From the above struture for all the PH name it should be 2,3 segments and for PH1 it is 3,4 needs to be replaced.TB1 table STR VAL NAME EXPECTED_STR     1.0.0.C.D A.B PH 1.A.B.C.D (Replace segment 2 and 3 with A and B) 1.0.0.C.D A.B PH1 1.0.A.B.D (Replace segment 3 and 4 with A and B) 1.0.0.C.D A.B PH2 A.0.0.B.D (Replace segment 1 and 4 with A and B) 1.0.0.C.D A.B PH3 A.0.0.C.B (Replace segment 1 and 5 with A and B) 1.0.0.C.D A.B PH4 1.0.0.A.B (Replace segment 4 and 5 with A and B) TB2 table NAME SEQ   PH 2,3 PH1 3,4 PH2 1,4 PH3 1,5 PH4 4,5
Thanks 
10. Re: How to concatnate the string in SQL or PL/SQL?
Solomon Yakobson May 19, 2012 12:38 PM (in response to 634151)
SY.with t1 as ( select name, seq, lvl, case when ','  seq  ',' like '%,'  lvl  ',%' then 1 end ind from tbl2, ( select level lvl from dual connect by level <= 5 ) ), t2 as ( select name, seq, lvl, case ind when 1 then sum(ind) over(partition by name order by lvl) end ind from t1 ) select str, tbl1.name, val, seq, rtrim(xmlagg(xmlelement(e,nvl(regexp_substr(val,'[^.]+',1,ind),regexp_substr(str,'[^.]+',1,lvl)),'.').extract('//text()') order by lvl),'.') new_str from tbl1, t2 where t2.name = tbl1.name group by str, tbl1.name, val, seq / STR NAME VAL SEQ NEW_STR      1.0.0.C.D PH A.B 2,3 1.A.B.C.D 1.0.0.C.D PH1 A.B 3,4 1.0.A.B.D 1.0.0.C.D PH2 A.B 1,4 A.0.0.B.D 1.0.0.C.D PH3 A.B 1,5 A.0.0.C.B 1.0.0.C.D PH4 A.B 4,5 1.0.0.A.B SQL>

11. Re: How to concatnate the string in SQL or PL/SQL?
Solomon Yakobson May 19, 2012 12:44 PM (in response to Solomon Yakobson)Actually, MODEL solution might be simpler:
SY.select str, name, val, seq, new_str from tbl1, tbl2 where tbl2.name = tbl1.name model partition by(row_number() over(order by tbl1.rowid) p) dimension by(1 d) measures(str,tbl1.name,val,seq,str new_str,0 ind) rules iterate(5) ( ind[1] = case when ','  seq[1]  ',' like '%,'  (iteration_number + 1)  ',%' then ind[1] + 1 else ind[1] end, new_str[1] = case when ','  seq[1]  ',' like '%,'  (iteration_number + 1)  ',%' then regexp_replace(new_str[1],'[^.]+',regexp_substr(val[1],'[^.]+',1,ind[1]),1,iteration_number + 1) else new_str[1] end ) order by name / STR NAME VAL SEQ NEW_STR      1.0.0.C.D PH A.B 2,3 1.A.B.C.D 1.0.0.C.D PH1 A.B 3,4 1.0.A.B.D 1.0.0.C.D PH2 A.B 1,4 A.0.0.B.D 1.0.0.C.D PH3 A.B 1,5 A.0.0.C.B 1.0.0.C.D PH4 A.B 4,5 1.0.0.A.B SQL>

12. Re: How to concatnate the string in SQL or PL/SQL?
634151 May 19, 2012 1:41 PM (in response to Solomon Yakobson)Hi Soloman,
It is awesome and working as expected. I have never known about this MODEL clause. Could you please provide some link to understand about this?
Also please let me know about the performance if i run the query for large volume of records? I am not getting the below sections and explain me how it will work?
dimension by(1 d)
rules iterate(6)
Thanks 
13. Re: How to concatnate the string in SQL or PL/SQL?
Solomon Yakobson May 19, 2012 4:17 PM (in response to 634151)user212310 wrote:
MODEL clause was introduced in 10g. Same as analytic functions MODEL works over resultset which it interplets as a spreadsheet. But unlike analytic functions it allows to base cell values based on other cell(s) and also allows iterations. I admit, it could be better documented with more explanations and examples, but again these are manuals and not tutorials. I don't follow it much, so I don't know any good tutorials on MODEL. Now explanation of this MODEL solution:
It is awesome and working as expected. I have never known about this MODEL clause. Could you please provide some link to understand about this?
1. PARTITION BY splits all rows into partitions. Here we want to partition by TBL1.STR. In fact, I overcomplicated it  all I needed was:
partition by(tbl1.rowid p)
2. DIMENSION BY. It uniquely identifies each cell within partition. Since TBL1 & TBL2 join is onetoone, each partition has just one row, therefore I used:
dimension by(1 d)
3. MEASURES defines spreadsheet columns.
4. RULES sets formulas how to calculate cell values. Here I uses iterate rules (5 iterations) since I know upfront each str has 5 parts. So we iterate over str 5 times and if needed replace corresponding part with proper value from val. Cell ind rule checks if current current part (we use iteration number + 1 since iterations are numbered starting zero) is present in seq and therefore needs to be replaced and if it does, and which part of val it needs to be replaced with. Cell new_str rule does, if needed, part replacement. So, for example, new_str[1]='1.0.0.C.D', name[1]='PH', seq[1]='2,3' and val[1]='A.B'. First iteration checks if 1 is preset in seq[1]. Since it is not present, ind[1] remains 0. Same way since 1 is not present in seq[1], new_str[1] remains '1.0.0.C.D'. Now we do second iteration. Now, 2 is present in seq[1], therefore ind[1] is assigned value 1. Same way since 2 is present in seq[1], part 2 of new_str[1]='1.0.0.C.D' is replaced with part 1 (ind[1]=1) of val[1] (which is 'A') and new_str[1] becomes '1.A.0.C.D'. Now we do third iteration, and again 3 is present in seq[1], ind[1] becomes 2 and new_str becomes '1.A.B.C.D'. Iterations 4 and 5 do not change new_str since they are not present in seq[1]. Then same logic is applied to next partition. I hope you get the picture.
Performacewise, you'll have to test it yourself. One thing I can tell, your design could be changed to improve performance. If TBL2 would store multiple rows per name, splitting seq into separate items. You could also split table TBL1 into TBL1A and TBL1B where TBL1A would store str and name and TBL1B would store str, name and separate parts of val.
SY. 
14. Re: How to concatnate the string in SQL or PL/SQL?
chris227 May 20, 2012 2:43 PM (in response to 634151)model without regexp and less iterations:
with t1 as ( select '1.0.0.C.D' str,'A.B' val, 'PH' name from dual union all select '1.0.0.C.D','A.B','PH1' name from dual union all select '1.0.0.C.D','A.B','PH2' name from dual union all select '1.0.0.C.D','A.B','PH3' name from dual union all select '1.0.0.C.D','A.B','PH4' name from dual) , t2 as ( select 'PH' name,'2,3' seq from dual union all select 'PH1','3,4' from dual union all select 'PH2','1,4' from dual union all select 'PH3','1,5' from dual union all select 'PH4','4,5' from dual ) select str from (select str,val,seq,t2.name name from t1,t2 where t1.name=t2.name) model dimension by (name n) measures(str,seq,val) rules iterate(2) (  2 = number of numbers in seq str[any]= substr( str[cv()] , 1 , substr(seq[cv()],1,1)*22 ) substr(val[cv()],((iteration_number+1)*21),1) substr( str[cv()] , substr(seq[cv()],1,1)*2 ) ,seq[any]=substr(seq[cv()],((iteration_number+1)*2+1),1) ) STR 1.A.B.C.D 1.0.A.B.D A.0.0.B.D A.0.0.C.B 1.0.0.A.B