-3
'HEADER|N1000|E1001|N1002|E1003|N1004|N1005'    
   'HEADER|N156|E1|N7|E122|N4|E5'   
   'HEADER|E0|E1|E2|E3|E4|E5'   
   'HEADER|N0|N1|N2|N3|N4|N5'
   'HEADER|N125'

How to extract the numbers in comma-separated format from this stringS?

Expected result:

1000,1001,1002,1003,1004,1005

How to extract the numbers with N or E as suffix/prefix ie. 

N1000

Expected result:

1000,1002,1004,1005

below regex does not return the result needed. But I want some thing like this

select REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005', '.*?(\d+)', '\1,'), ',?\.*$', '') from dual

the problem here is when i want numbers with E OR N

select REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005', '.*?N(\d+)', '\1,'), ',?\.*$', '') from dual

select REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005', '.*?E(\d+)', '\1,'), ',?\.*$', '') from dual

they give good results for this scenerio

but when i input 'HEADER|N1000|E1001' it gives wrong answer plzzz verify and correct it

amarender reddy jali
  • 29
  • 7

3 Answers3

0

Update

Based on the changes to the question, the original answer is not valid. Instead, the solution is considerably more complex, using a hierarchical query to extract all the numbers from the string and then LISTAGG to put back together a list of numbers extracted from each string. To extract all numbers we use this query:

WITH cte AS (
  SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, '[NE]\d+', 1, level) AS num FROM test
  CONNECT BY REGEXP_SUBSTR(data, '[NE]\d+', 1, level) IS NOT NULL
)
SELECT data, LISTAGG(SUBSTR(num, 2), ',') WITHIN GROUP (ORDER BY l) AS "All numbers"
FROM cte
GROUP BY data

Output (for the new sample data):

DATA                                        All numbers
HEADER|E0|E1|E2|E3|E4|E5                    0,1,2,3,4,5
HEADER|N0|N1|N2|N3|N4|N5                    0,1,2,3,4,5
HEADER|N1000|E1001|N1002|E1003|N1004|N1005  1000,1001,1002,1003,1004,1005
HEADER|N125                                 125
HEADER|N156|E1|N7|E122|N4|E5                156,1,7,122,4,5

To select only numbers beginning with E, we modify the query to replace the [EN] in the REGEXP_SUBSTR expressions with just E i.e. 

  SELECT DISTINCT data, level AS l, REGEXP_SUBSTR(data, 'E\d+', 1, level) AS num FROM test
  CONNECT BY REGEXP_SUBSTR(data, 'E\d+', 1, level) IS NOT NULL

Output:

DATA                                        E-numbers
HEADER|E0|E1|E2|E3|E4|E5                    0,1,2,3,4,5
HEADER|N0|N1|N2|N3|N4|N5    
HEADER|N1000|E1001|N1002|E1003|N1004|N1005  1001,1003
HEADER|N125     
HEADER|N156|E1|N7|E122|N4|E5                1,122,5

A similar change can be made to extract numbers commencing with N.

Demo on dbfiddle

Original Answer

One way to achieve your desired result is to replace a string of characters leading up to a number with that number and a comma, and then replace any characters from the last ,| to the end of string from the result:

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?(\d+)', '\1,'), ',?\|.*$', '') FROM dual

Output:

1000,1001,1002,1003,1004,1005

To only output the numbers beginning with N, we add that to the prefix string before the capture group:

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?N(\d+)', '\1,'), ',?\|.*$', '') FROM dual

Output:

1000,1002,1004,1005

To only output the numbers beginning with E, we add that to the prefix string before the capture group:

SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?E(\d+)', '\1,'), ',?\|.*$', '') FROM dual

Output:

1001,1003

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95
  • Comments are not for extended discussion or debugging sessions; this conversation has been [archived in chat](https://chat.stackoverflow.com/rooms/200927/discussion-on-answer-by-nick-using-sql-extract-numbers-comma-separated-from-st). – Cody Gray - on strike Oct 16 '19 at 03:39
  • @nick i have added the new data sets .for which the query should satisfy – amarender reddy jali Oct 16 '19 at 03:57
  • @CodyGray OP doesn't have enough reputation to visit a chat room... I assume that is why the "would you like to move this discussion to chat" option was never offered. – Nick Oct 16 '19 at 04:32
  • @amarenderreddyjali I've updated my answer. It should be a lot more robust now (and works for all the examples you have given) – Nick Oct 16 '19 at 05:35
  • @nick thanks but i am thinking of using ur previous answer and ''' select * from (SELECT REGEXP_REPLACE(REGEXP_REPLACE('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '.*?E(\d+)', '\1,'), ',?\|.*$', '') as mum FROM dual) where mum like '%|E% ''' – amarender reddy jali Oct 16 '19 at 07:28
  • @ NICK can u answer this https://stackoverflow.com/questions/58410091/how-to-make-data-1-2-3-into-list-of-integers-such-that-select-from-employees – amarender reddy jali Oct 16 '19 at 09:47
  • @NICK how can we group 5 pm yesterday to 5 pm today records to todays date https://stackoverflow.com/q/58497919/12212199?sem=2 – amarender reddy jali Oct 22 '19 at 06:12
0

I don't know what DBMS you are using, but here's one way to do it in Postgres:

WITH cte AS (
  SELECT CAST('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|' AS VARCHAR(1000)) AS myValue
)
SELECT SUBSTRING(MyVal FROM 2)
FROM (
  SELECT REGEXP_SPLIT_TO_TABLE(myValue,'\|') MyVal
  FROM cte
) src
WHERE SUBSTRING(MyVal FROM 1 FOR 1) = 'N'
;

SQL Fiddle

ravioli
  • 3,749
  • 3
  • 14
  • 28
0

As Far as I have understood the question , you want to extract substrings starting with N from the string, You can try following (And then you can merge the output seperated by commas if needed)

select REPLACE(value, 'N', '') from STRING_SPLIT('HEADER|N1000|E1001|N1002|E1003|N1004|N1005|', '|') where value like 'N%'

OutPut :

1000 1002 1004 1005

Hammad Raza
  • 1
  • 4