Current Position:Home > Need help in finding the position of a SubString in a String

Need help in finding the position of a SubString in a String

Update:11-30Source: network consolidation
Advertisement
Hi All,
I have a VARCHAR2 column in my table which has data similar to '152-425-3265-8-5623-45'.
I want to find the position of the numbers in the column.
For example:
If i give 8 - query should return me 4.
If i give 425 - query should return me 2.
The numbers are delimited with '-' value.
Please help.
Thanks in advance.
Edited by: 868171 on Jun 24, 2011 4:33 AM
Edited by: 868171 on Jun 24, 2011 4:34 AM

The Best Answer

Advertisement
do you know the max number of dashes?
if so
with t as (select  '152-425-3265-8-5623-45' code, '425' num from dual union
               select  '152-425-3265-8-5623-45' code, '8' num from dual)
select code, num, case num when  regexp_substr( code,'[^\-]+',1,1) then '1'
               when  regexp_substr( code,'[^\-]+',1,2)  then '2'
               when  regexp_substr( code,'[^\-]+',1,3) then '3'
               when  regexp_substr( code,'[^\-]+',1,4)  then '4'
               when  regexp_substr( code,'[^\-]+',1,5) then '5'
               when  regexp_substr( code,'[^\-]+',1,6)  then '6'
               else 'not found' end result
from t
CODE     NUM     RESULT
152-425-3265-8-5623-45     425     2
152-425-3265-8-5623-45     8     4or if you have 11g you can use regexp_count
with t as (select  '152-425-3265-8-5623-45' code, '425' num from dual union
               select  '152-425-3265-8-5623-45' code, '8' num from dual)
select code, num, regexp_count(regexp_substr(code,'^.*'||num||'.'),'-')
from tEdited by: pollywog on Jun 24, 2011 7:55 AM
  • Need help in finding the position of a SubString in a String Update:11-30

    Hi All, I have a VARCHAR2 column in my table which has data similar to '152-425-3265-8-5623-45'. I want to find the position of the numbers in the column. For example: If i give 8 - query should return me 4. If i give 425 - query should return me 2.

  • Is it possible to get the position of a charcter from a string ? Update:10-11

    Hi ! Is it possible to get the position of a charcter from a string ? Example: @VAR contains the following text "ABCDEFGHIJKLM" I'am seaching for FGH, it start on pos. 6 en ends on pos 8. How can I get these numbers 6 and 8 ? So, I can use them

  • To get the position of the characters in the string in sql Update:11-30

    hi all, I need to get the position of the characters in the sql , the string like this '10101110111010111' , is it possible to get the answer like this pos char 1 1 2 0 3 1 4 0 and so on . Thanks in advance .select level pos,substr(:your_string,level

  • Finding the position of an integer in a string Update:10-11

    I haven't done much Java programming for the past 3 months, but I'm trying to get back into it now, but I'm having some trouble. I'm trying to break up a string of integers, to store each individual integer in an array. So, if I had something like 13

  • Count the position of substring Update:11-30

    Hi, Is there any way to count the position of the substring in a string? I have a requirement where the string I419II/I423II/ZCTOII/I704II/S415II/J001II/ZCSCDII/ZCSCOII has to be split and put into 2 fields function and department.The function field

  • Find a string Update:11-30

    Hi Everyone: Can somebody tell me how can i get the position of a substring in a string?Hi You might want to look at Instr function in PLSQL HTH Arvind BalaramanRead other 2 answers

  • Instr Update:11-30

    Hi, in Oracle, instr is to return the exact position of one substring within another string. How to achieve the same in Crystal report syntax? Thanks & Best RegardsHi 1.Create a formula 2.In the formula you can have the "InStr" itself . InSt

  • Oracle replacement for PATINDEX Update:11-30

    Hi, does anyone know an equivallent for PATINDEX from sql server ? let me remind you: PATINDEX ( '%pattern%' , expression ) in t-sql is sometring like instr, but it supports wildcards. I need something like this in oracle. Please help Thanks, pesqno

  • DnD - Get Drop Mouse Position Update:11-30

    I want to Drag some image file from a JList, and then drop it on a JPanel. The dropping on the panel functions. The problem is, i would like to get the position of the mouse, when the item is dropped on the panel. I use the panel to visualize graphic

  • Position of comma symbol Update:11-30

    Hi all, I'm looking for position of comma symbol in varchar2 string. How do I perform it?marco wrote: Nope, it has to do with extraction of street name from mixed address column.In that case REGEXP_INSTR or REGEXP_REPLACE might be even more helpful t