extract serial from string SQL Netezza

Question

please I need a query that could extract the Serial # from the Logmsg, the hard thing is that it does not follow a specific pattern, there is not fixed delimiter and the serial length is not the same always

|LogMsg                                                                                    |
|------------------------------------------------------------------------------------------|
|Customer Receive CPE Indoor. serial 21530369847SKA011094, user:ahmed.o.haraz              |
|Customer Receive CPE Indoor as change. serial :21530369847SK9078291, user:Abdullah.M160275|
|Customer Receive CPE Indoor as change. serial :T5D7S18802909825, user:ahmed.o.haraz       |
|Customer Receive CPE Indoor as change. serial :T5D7S18802909830, user:ahmed.o.haraz       |
|Customer Receive CPE Indoor. serial ZTERRTHJ9303771, user:Mohamed.E176246                 |
|Customer Returned CPE. serial :21530369847SKA011094, user:ahmed.o.haraz                   |

the result will be like below:

|Serial              |
|--------------------|
|21530369847SKA011094|
|21530369847SK9078291|
|T5D7S18802909825    |
|T5D7S18802909830    |
|ZTERRTHJ9303771     |
|21530369847SKA011094|

You need a RegEx, see https://stackoverflow.com/q/37622585/2527905 Netezza supports regexp_extract(), e.g. `regexp_extract(logmsg, '\d[^,]*')` — dnoeth, Oct 07 '20 at 09:50
it seems that extract the int from the string as if the serial starts with letters, it will extract only the int like this serial: ZTERRTHJ9303771 will be 9303771 — Ahmed Mohammed Abdel Kader, Oct 07 '20 at 11:28
yes, it not located in each serial. the query provided by Gordon is works well — Ahmed Mohammed Abdel Kader, Oct 07 '20 at 11:54

Gordon Linoff · Accepted Answer · 2020-10-07T11:11:03.627

1

One method is regexp_replace(), but you have different formats for what follows serial. So:

select replace(replace(regexp_extract(logmsg, 'serial [^,]+'), 'serial ', ''), ':', '')

edited Oct 07 '20 at 11:11

answered Oct 07 '20 at 10:59

Gordon Linoff

1,242,037
58
646
786

please Gordon the provided query didn't work with this sample of Serial: Customer Receive CPE Indoor. serial:ZTERRT1H9202990 – Ahmed Mohammed Abdel Kader Oct 08 '20 at 00:05
1

@AhmedMohammedAbdelKader . . . Perhaps you should ask a new question with more examples. – Gordon Linoff Oct 08 '20 at 00:27

score 1 · Answer 2 · answered Oct 08 '20 at 02:37

1

This is answered in https://stackoverflow.com/a/64254966/14311638

Use a combination of regexp_extract_all_sp and get_value_varchar along with the right regex pattern

answered Oct 08 '20 at 02:37

Aniket Kulkarni

471
2
2

extract serial from string SQL Netezza

2 Answers2