Thanks Gopa, able to extract needed string now.
On Sun, Dec 12, 2010 at 4:38 AM, Gopakumar Pandarikkal < [email protected]> wrote: > Regular expressions (pattern matching) is the answer > first alphabet of the first string that matches the pattern is taken then > added with next occurences of the same. > > SELECT substr(REGEXP_substr('Axcdr-Bmm_km-Ckiok_uijj', > '([A-Z][^-]*)',1,1),1,1) ||substr(REGEXP_substr('Axcdr-Bmm_km-Ckiok_uijj', > '([A-Z][^-]*)',1,2),1,1) || substr(REGEXP_substr('Axcdr-Bmm_km-Ckiok_uijj', > '([A-Z][^-]*)',1,3),1,1)||substr(REGEXP_substr('Axcdr-Bmm_km-Ckiok_uijj', > '([A-Z][^-]*)',1,4),1,1) abbrevcol > FROM dual; > > > > On Sun, Dec 12, 2010 at 12:19 PM, gayathri Dev <[email protected]> wrote: > >> thanks for a quick response David. >> >> My mistake, it was not a good example... >> >> Please use the below examples. >> >> -> "Axcdr-Bmm_km-Ckiok_uijj" >> I need ABC from the above string. >> >> >> -> "Axcdr" >> Expected Ouput : A >> >> -> "Axcdr-Bmm_km_fg" >> Expected Ouput : AB >> >> >> *1st character after a hypen needs to be extracted.* >> >> -G >> >> >> On Sat, Dec 11, 2010 at 10:02 PM, ddf <[email protected]> wrote: >> >>> >>> >>> On Dec 11, 9:52 pm, gayathri Dev <[email protected]> wrote: >>> > Again, its another issue. >>> > >>> > select 'Atext-Btext-Ctext-Dtext' from dual; >>> > >>> > Expected Output: >>> > ABCD. >>> > >>> > select 'Atext' from dual; >>> > >>> > Expected Output: >>> > A >>> > >>> > select 'Atext-Btext' from dual; >>> > >>> > Expected Output: >>> > AB >>> > >>> > Want to extract first character after hypen(-) >>> > I want to achieve this result from Oracle 10g SQL . Please suggest a >>> quick >>> > option. >>> > >>> > Thanks >>> > `G >>> >>> >>> SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 12 01:00:22 2010 >>> >>> Copyright (c) 1982, 2007, Oracle. All Rights Reserved. >>> >>> >>> Connected to: >>> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit >>> Production >>> With the Partitioning, OLAP, Data Mining and Real Application Testing >>> options >>> >>> SQL> select replace(replace('&1','text',''),'-','') alpha from dual; >>> Enter value for 1: Atext-Btext-Ctext-Dtext >>> old 1: select replace(replace('&1','text',''),'-','') alpha from >>> dual >>> new 1: select replace(replace('Atext-Btext-Ctext- >>> Dtext','text',''),'-','') alpha from dual >>> >>> ALPHA >>> >>> -------------------------------------------------------------------------------- >>> ABCD >>> >>> SQL> / >>> Enter value for 1: Atext >>> old 1: select replace(replace('&1','text',''),'-','') alpha from >>> dual >>> new 1: select replace(replace('Atext','text',''),'-','') alpha from >>> dual >>> >>> ALPHA >>> >>> -------------------------------------------------------------------------------- >>> A >>> >>> SQL> / >>> Enter value for 1: Atext-Btext >>> old 1: select replace(replace('&1','text',''),'-','') alpha from >>> dual >>> new 1: select replace(replace('Atext-Btext','text',''),'-','') alpha >>> from dual >>> >>> ALPHA >>> >>> -------------------------------------------------------------------------------- >>> AB >>> >>> SQL> >>> >>> >>> David Fitzjarrell >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Oracle PL/SQL" group. >>> To post to this group, send email to [email protected] >>> To unsubscribe from this group, send email to >>> [email protected] >>> For more options, visit this group at >>> http://groups.google.com/group/Oracle-PLSQL?hl=en >> >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > > > -- > Gopakumar P.G. > > > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
