OraFAQ Forum: SQL & PL/SQL » SQL query to use Reg

Home » SQL & PL/SQL » SQL & PL/SQL » SQL query to use Reg_exp (Oracle 10g/11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL query to use Reg_exp [message #617250]

Thu, 26 June 2014 09:09

dorerajp
Messages: 8
Registered: June 2014

Junior Member

Hi

Could you please help to write a SQL that if i pass Priority = 4 the O/P could be "96" from SLA column
Below data sample for your reference.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Priority SLA Output
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4 2-4-72-96-100 96
5 2-4-16-40-80 80
3 1-2-4-8-40 4
1 2-4-72-96-100 2
5 2-4-16-40-80 80
5 2-4-16-40-80 80

Report message to a moderator

Re: SQL query to use Reg_exp [message #617252 is a reply to message #617250]

Thu, 26 June 2014 09:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: SQL query to use Reg_exp [message #617253 is a reply to message #617252]

Thu, 26 June 2014 09:12

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Simply use SUBSTR. And please post what you have tried so far.

[Updated on: Thu, 26 June 2014 09:13]

Report message to a moderator

Re: SQL query to use Reg_exp [message #617255 is a reply to message #617253]

Thu, 26 June 2014 09:23

dorerajp
Messages: 8
Registered: June 2014

Junior Member

Thanks for reply ,

Currenlt i'm using below logic, but in case priority = 4 and SLA= 2-4-16-100-120 then 100 should be my O/P , but this will give 10,so this is not correct O/P.

(inner1_5.SR_PRIORITY,1,SUBSTR(inner1_5.PEP_SLO_ACTION,1,1),2,SUBSTR(inner1_5.PEP_SLO_ACTION,3,1),3,SUBSTR(inner1_5.PEP_SLO_ACTION,5, 2),4,SUBSTR(inner1_5.PEP_SLO_ACTION,8,2),5,SUBSTR(inner1_5.PEP_SLO_ACTION,11,2)

Report message to a moderator

Re: SQL query to use Reg_exp [message #617256 is a reply to message #617255]

Thu, 26 June 2014 09:28

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You need to use instr to get the position of the seperators surrounding the number, then use that to do the substr.

Report message to a moderator

Re: SQL query to use Reg_exp [message #617257 is a reply to message #617255]

Thu, 26 June 2014 09:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It is flawed & fatal design to store multiple values in a single column.

value of 4 exists in more than 1 row in SLA column, so what determines which row is included in result set?

Report message to a moderator

Re: SQL query to use Reg_exp [message #617259 is a reply to message #617257]

Thu, 26 June 2014 09:33

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@OP, Per example data, seems to be duplicate rows for priority 5. For just single row you need to limit rows with ROWNUM.

Report message to a moderator

Re: SQL query to use Reg_exp [message #617262 is a reply to message #617253]

Thu, 26 June 2014 09:38

dorerajp
Messages: 8
Registered: June 2014

Junior Member

Hi ,

Below Query i have tried

select
PRIORITY_CODE,
DECODE (PRIORITY_CODE,1,SUBSTR(PEP_SLO_ACTION,1,1),2,SUBSTR(PEP_SLO_ACTION,3,1),3,SUBSTR(PEP_SLO_ACTION,5,2),4,SUBSTR(PEP_SLO_ACTION,8,2),5, SUBSTR(PEP_SLO_ACTION,11,2)) val
from PROBSUMMARYM1
where 1=1
and PROBLEM_STATUS='Accepted'

Report message to a moderator

Re: SQL query to use Reg_exp [message #617263 is a reply to message #617262]

Thu, 26 June 2014 09:41

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

No test case, no code tags. Makes me feel less interested to help. CM told you to use INSTR to get the position, did you try? I don't see.

Report message to a moderator

Re: SQL query to use Reg_exp [message #617264 is a reply to message #617262]

Thu, 26 June 2014 09:44

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

REGEXP_SUBSTR(PEP_SLO_ACTION,'\d+',1,PRIORITY_CODE)

SY.

Report message to a moderator

Re: SQL query to use Reg_exp [message #617297 is a reply to message #617255]

Fri, 27 June 2014 00:07

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

dorerajp wrote on Thu, 26 June 2014 19:53

Currenlt i'm using below logic, but in case priority = 4 and SLA= 2-4-16-100-120 then 100 should be my O/P , but this will give 10,so this is not correct O/P.

(inner1_5.SR_PRIORITY,1,SUBSTR(inner1_5.PEP_SLO_ACTION,1,1),2,SUBSTR(inner1_5.PEP_SLO_ACTION,3,1),3,SUBSTR(inner1_5.PEP_SLO_ACTION,5, 2),4,SUBSTR(inner1_5.PEP_SLO_ACTION,8,2),5,SUBSTR(inner1_5.PEP_SLO_ACTION,11,2)

SY's regexp method looks neat and simple. For the age old substr, instr method(looks horrible though) :

SQL> def n=4;
SQL> 
SQL> WITH DATA AS(
  2  SELECT 4 priority, '2-4-72-96-100' sla FROM dual UNION ALL
  3  SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  4  SELECT 3, '1-2-4-8-40' sla FROM dual UNION ALL
  5  SELECT 1, '2-4-72-96-100' sla FROM dual UNION ALL
  6  SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  7  SELECT 5, '2-4-16-40-80' sla FROM dual)
  8  SELECT Substr(sla, Instr(sla, '-', 1, 3) + 1, Instr(sla, '-', 1, 4) -
  9                                                       Instr(sla, '-', 1, 3) - 1) op
 10  FROM   data
 11  WHERE  priority = &n;
OP
-------------
96

SQL> def n=5;
SQL> 
SQL> WITH DATA AS(
  2  SELECT 4 priority, '2-4-72-96-100' sla FROM dual UNION ALL
  3  SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  4  SELECT 3, '1-2-4-8-40' sla FROM dual UNION ALL
  5  SELECT 1, '2-4-72-96-100' sla FROM dual UNION ALL
  6  SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  7  SELECT 5, '2-4-16-40-80' sla FROM dual)
  8  SELECT Substr(sla, Instr(sla, '-', 1, 3) + 1, Instr(sla, '-', 1, 4) -
  9                                                       Instr(sla, '-', 1, 3) - 1) op
 10  FROM   data
 11  WHERE  priority = &n;
OP
-------------
40
40
40

Report message to a moderator

Re: SQL query to use Reg_exp [message #617312 is a reply to message #617264]

Fri, 27 June 2014 04:56

dorerajp
Messages: 8
Registered: June 2014

Junior Member

Thanks Dude for your valuable help.

i have attached the screen shot and this is simple and working fine. /forum/fa/11990/0/

Attachment: Result.jpg
(Size: 13.02KB, Downloaded 828 times)

Report message to a moderator

Re: SQL query to use Reg_exp [message #617324 is a reply to message #617312]

Fri, 27 June 2014 07:12

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

XML solutions:

SQL> WITH DATA AS(
  2               SELECT 4 priority, '2-4-72-96-100' sla FROM dual UNION ALL
  3               SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  4               SELECT 3, '1-2-4-8-40' sla FROM dual UNION ALL
  5               SELECT 1, '2-4-72-96-100' sla FROM dual UNION ALL
  6               SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  7               SELECT 5, '2-4-16-40-80' sla FROM dual
  8              )
  9  SELECT  XMLQUERY(
 10                   'for $z at $i in ora:tokenize($sla,"-")
 11                      where $i = $p
 12                      return $z'
 13                   passing sla as "sla",
 14                           priority as "p"
 15                   returning content
 16                  ) x
 17    FROM  DATA
 18  /

X
--------------------------------------------------------------------------------
96
80
4
2
80
80

6 rows selected.

SQL>

SQL> WITH DATA AS(
  2               SELECT 4 priority, '2-4-72-96-100' sla FROM dual UNION ALL
  3               SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  4               SELECT 3, '1-2-4-8-40' sla FROM dual UNION ALL
  5               SELECT 1, '2-4-72-96-100' sla FROM dual UNION ALL
  6               SELECT 5, '2-4-16-40-80' sla FROM dual UNION ALL
  7               SELECT 5, '2-4-16-40-80' sla FROM dual
  8              )
  9  SELECT  x.*
 10    FROM  DATA,
 11          XMLTABLE(
 12                   'for $z at $i in ora:tokenize($sla,"-")
 13                      where $i = $p
 14                      return $z'
 15                   passing sla as "sla",
 16                           priority as "p"
 17                  ) x
 18  /

COLUMN_VALUE
--------------------------------------------------------------------------------
96
80
4
2
80
80

6 rows selected.

SQL>

SY.

Report message to a moderator

Previous Topic:	How to enforce key uniqueness in a temporal table?
Next Topic:	find all the null data value in column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 21:10:59 CDT 2024